HomeMySQL Page 13 - Data Definition Language, Part 1
4.10.6 Automatic Type Conversion and Value Clipping - MySQL
Studying for the MySQL Certification exam? This article, the first of two parts, covers roughly 10 percent of the material that will appear on the exam, including general database and table properties, storage engines and table types, and more. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois et. al. (Sams, 2004, ISBN: 0672326329).
For historical reasons, MySQL is forgiving about signaling an error if a given value doesn't match the datatype of the column that is the insert target. Instead, MySQL does its best to perform automatic type conversion. For example, if you attempt to store a negative value in an UNSIGNED integer column, MySQL silently converts it to zero, which is the nearest legal value for the column. In other words, the MySQL server converts input values to the types expected from the column definitions, inserts the result, and continues on its way.
If you need to prevent attempts to insert invalid values into a table, you should first validate the values on the client side; however, because that isn't an exam topic, it isn't discussed further here.
This section describes the kinds of conversions that MySQL performs and the circumstances under which they occur. After you know these principles, you'll know what types of validation are necessary before trying to store your data in a MySQL database.
In many cases, type conversion affords you the flexibility to write a statement different ways and get the same result. For example, if i is an integer column, the following statements both insert 43 into it, even though the value is specified as a number in one statement and as a string in the other:
INSERT INTO t (i) VALUES(43);
INSERT INTO t (i) VALUES('43');
MySQL performs automatic string-to-number conversion for the second statement.
In other cases, the effects of type conversion might be surprising, particularly if you're unaware that these conversions occur. You can avoid such surprises by understanding the conditions under which conversion takes place. In general, MySQL performs type conversion based on the constraints implied by a column's definition. These constraints apply in several contexts:
When you insert or update column values with statements such as INSERT, REPLACE, UPDATE, or LOAD DATA INFILE.
When you change a column definition with ALTER TABLE.
When you specify a default value using a DEFAULT value option in a column definition. (For example, if you specify a negative default for an UNSIGNED column, the value is converted, resulting in a default of zero.)
The following list discusses some of the conversions that MySQL performs. It isn't exhaustive, but is sufficiently representative to provide you with a good idea of how MySQL treats input values and what you'll be tested on in the exam. Circumstances under which automatic type conversion occurs include the following:
Conversion of out-of-range values to in-range values.If you attempt to store a value that's smaller than the minimum value allowed by the range of a column's datatype, MySQL stores the minimum value in the range. If you attempt to store a value that's larger than the maximum value in the range, MySQL stores the range's maximum value. Some examples of this behavior are as follows:
TINYINT has a range of –128 to 127. If you attempt to store values less than –128 in a TINYINT column, MySQL stores –128 instead. Similarly, MySQL stores values greater than 127 as 127.
If you insert a negative value into an UNSIGNED integer column, MySQL converts the value to 0.
When you reach the upper limit of an AUTO_INCREMENT column, an attempt to generate the next sequence value results in a duplicate-key error. This is a manifestation of MySQL's general out-of-range value clipping behavior. For example, assume that you have a TINYINT UNSIGNED column as an AUTO_INCREMENT column and that it currently contains 254 as the maximum sequence value. The upper limit for this column type is 255, so the next insert generates a sequence value of 255 and successfully stores it in the new record. However, the insert after that fails because MySQL generates the next sequence value, which is 256. Because 256 is higher than the column's upper limit of 255, MySQL clips 256 down to 255 and attempts to insert that value. But because 255 is already present in the table, a duplicate-key error occurs.
Conversion to datatype default.If you attempt to store a value for which MySQL cannot decide on an appropriate conversion, it stores the default value for the datatype of the target column. For example, if you try to store the value 'Sakila 'in an INT column, MySQL stores the value 0. For dates, the "zero" value is 0000-00-00 and for time columns 00:00:00. More details on the default for each column type are given in section 4.10.4, "Column Options."
String truncation.If you attempt to store a string value into a VARCHAR or CHAR column with a defined length that's shorter than the string, the string is truncated to fit the column's length. That is, only the leading characters that fit into the column are stored. The remaining characters are discarded. For example, if you try to store the value 'Sakila 'into a column defined as CHAR(4), MySQL stores the value 'Saki '.
Date and time interpretation.The server performs streamlined checking of temporal values. It looks at individual components of date and time values, but does not perform an exhaustive check of the value as a whole. For example, day values may be considered valid as long as they're within the range 1 to 31. This means you can specify a date such as '2000-04-31 'and MySQL will store it as given. However, a DATETIME value such as '2000-01-01 24:00:00 'contains an hour value of 24, which is never valid as a time of day. Consequently, MySQL stores the "zero" value in DATETIME format ( '0000-00-00 00:00:00 ').
Addition of century for two-digit years.Like many other computer programs, MySQL converts two-digit years to four-digit years. Values 00 to 69 are converted to 2000-2069; values 70 to 99 are converted to 1970-1999.
Enumeration and set value conversion.If a value that's assigned to an ENUM column isn't listed in the ENUM definition, MySQL converts it to ''(the empty string). If a value that's assigned to a SET column contains elements that aren't listed in the SET definition, MySQL discards those elements, retaining only the legal elements.
Handing assignment ofNULL toNOT NULL columns.The effect of assigning NULL to a NOT NULL column depends on whether the assignment occurs in a single-row or multiple-row INSERT statement. For a single-row INSERT, the statement fails. For a multiple-row INSERT, the column is assigned the default value for the column type.
Conversion of fixed-point values.Conversion, into numbers, of string values that can be interpreted as numbers is different for DECIMAL than for other numeric datatypes. This occurs because DECIMAL values are represented as strings rather than in native binary format. For example, if you assign '0003 'to an INT or FLOAT, it's stored as 3 in integer or floating-point binary format. In contrast, if you assign '0003 'to a DECIMAL column, it's stored without change, including the leading zeros, even though it will behave identically to a '3 'in mathematical operations. If the DECIMAL column isn't wide enough to accommodate the leading zeros, as many are stored as possible. If you store '0003 'into a DECIMAL(2,0) UNSIGNED column, it's converted to '03 '.
Using ALTER TABLE to change a column's datatype maps existing values to new values according to the constraints imposed by the new datatype. This might result in some values being changed. For example, if you change a TINYINT to an INT, no values are changed because all TINYINT values fit within the INT range. However, if you change an INT to a TINYINT, any values that lie outside the range of TINYINT are clipped to the nearest endpoint of the TINYINT range. Similar effects occur for other types of conversions, such as TINYINT to TINYINT UNSIGNED (negative values are converted to zero), and converting a long string column to a shorter one (values that are too long are truncated to fit the new size).
If a column is changed to NOT NULL using ALTER TABLE, MySQL converts NULL values to the default value for the column type.
The following table shows how several types of string values are handled when converted to date or numeric datatypes. It demonstrates several of the points just discussed. Note that only string values that look like dates or numbers convert properly without loss of information. Note too that leading zeros are retained for the DECIMAL column during conversion.
Converted to DATE
Converted to INT
Converted to DECIMAL
'500 hats '
This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.