inserted row into an AUTO_INCREMENT column starts at 1 and increases by 1 for every record that is
inserted into the table. For example, you create a table with an AUTO_INCREMENT column. You add a row of
data. The AUTO_INCREMENT column's value is 1. You insert another record, and the value is now 2. You
delete the very first row of data, and insert another record. What do you think the value of the new row is? If
you guessed 3 , you're right. Now you delete the newly inserted row—the row whose AUTO_INCREMENT
value was 3 , and immediately insert another row. What do you think the value of the row is? If you guessed
4 , you're right. The AUTO_INCREMENT column does not reuse the maximum value if you delete it. It will not
reuse a value that is not the maximum value.
To take advantage of the AUTO_INCREMENT feature, you must use a NULL, 0 , or a blank space in the
field that is AUTO_INCREMENTed when inserting a new row. For example, the following will take
advantage of the AUTO_INCREMENT function:
CREATE TABLE Test (Auto_Test int NOT NULL AUTO_INCREMENT);
INSERT INTO Test (Auto_Test) values(NULL);
INSERT INTO TEST (Auto_Test) values(0);
INSERT INTO Test (Auto_Test) values();
You can also include a number. If the number already exists, you will get an error. If it doesn't, it will be
inserted. If the value is the highest value in the column, the next value that is inserted will be one more
than the current value. So, if you wanted to have an AUTO_INCREMENT modified column that begins will
9000, your very first record would have to be 9000 and all subsequent records would be incremented by
one. You can explicitly declare a starting point by using the following syntax:
CREATE TABLE Test
(Test_ID INT NOT NULL AUTO_INCREMENT
AUTO_INCREMENT = 9000,
Another_Column INT)
It doesn't matter where you place the AUTO_INCREMENT= nnnn in your creation syntax. There can be
only one AUTO_INCREMENT column in a table—MySQL is smart enough to know where to use this
value.
To get the most recently added sequence number, use the LAST_INSERT_ID() function. This function
will return the sequence number that was added last by you. What this means is that this function will
return the last record you inserted, not anyone else who may be using the database. This is a handy
function to have. Even if someone else inserts a record immediately after you do, this function will return
the last record that you inserted. This function is tied to your session, so if you lose your session after
you just inserted a record and you try to call this function after you reconnect, you will get a zero,
because, according to MySQL, you haven't inserted anything.
One final thing to know about the AUTO_INCREMENT modifier is that it does not start over. So if you
reach the maximum value of the data type you picked, you will receive an error. For example, if you
select a TINYINT for an AUTO_INCREMENT column, the maximum value it can have is 255. After the
255th record has been added and you try to add another one, MySQL is going to produce an error. To
avoid this, use the INT type. You should have more than enough numbers.
The AUTO_INCREMENT modifier only works on integer numbers. FLOATs, DOUBLEs, and DECIMALs
cannot be used in an AUTO_INCREMENT column.
ZEROFILL
The ZEROFILL column modifier is used to display leading zeros of a number based on the display width. As
mentioned earlier, all numeric types have an optional display width. For example, if you declare an INT(8)
ZEROFILL, and the value you're storing is 23, it will be displayed as 00000023. This feature is useful when
you need to display the value in its entirety, in sequence numbers or when you need to display information to
the user, for example.
Character or String Data Types
The other major group of data types are strings or character types. A string is a set of characters. A string
type can store data like Kentucky or 922 Westbrook Ln. Pretty much any value can be stored in a string data
type. Again, size is a factor when determining which string type you are going to use. The maximum size and
storage specifications are listed in Table 7.3. The storage that is needed for each type is determined by the
length of the string.
Table 7.3 String Types
Type Name Max Storage