| [ Team LiB ] |
|
1.4 Data TypesFor each data type, the syntax shown uses square brackets ([ ]) to indicate optional parts of the syntax. The following example shows how BIGINT is explained in this chapter: BIGINT[(display_size)] This indicates that you can use BIGINT alone or with a display size value. The italics indicate that you do not enter display_size literally, but instead enter your own value. Possible uses of BIGINT include: BIGINT BIGINT(20) In addition to the BIGINT type, many other MySQL data types support the specification of a display size. Unless otherwise specified, this value must be an integer between 1 and 255. In the following cases, MySQL silently changes the column type you specify in your table creation to something else:
1.4.1 NumericsMySQL supports all ANSI SQL2 numeric data types. MySQL numeric types break down into two groups: integer and floating point. Within each group, the types differ by the amount of storage required for them. Numeric types allow you to specify a display size, which affects the way MySQL displays results. The display size bears no relation to the internal storage provided by each data type. In addition, the floating types allow you to optionally specify the number of digits that follow the decimal point. In such cases, the digits value should be an integer from 0 to 30 that is at most two less than the display size. If you do make the digits value greater than two less than the display size, the display size will automatically change to two more than the digits value. For instance, MySQL automatically changes FLOAT(6,5) to FLOAT(7,5). When you insert a value into a column that requires more storage than the data type allows, it will be clipped to the minimum (negative values) or maximum (positive values) value for that data type. MySQL will issue a warning when such clipping occurs during ALTER TABLE, LOAD DATA INFILE, UPDATE, and multirow INSERT statements. The AUTO_INCREMENT attribute may be supplied for at most one column of an integer type in a table. The UNSIGNED attribute may be used with any numeric type. An unsigned column may contain only positive integers or floating-point values. The ZEROFILL attribute indicates that the column should be left padded with zeros when displayed by MySQL. The number of zeros padded is determined by the column's display width.
Storage8 bytes DescriptionLargest integer type, supporting range of whole numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (0 to 18,446,744,073,709,551,615 unsigned). MySQL performs all arithmetic using signed BIGINT or DOUBLE values, but BIGINT has performing arithmetic on unsigned values. You should therefore avoid performing any arithmetic operations on unsigned BIGINT values greater than 9,223,372,036,854,775,807. If you do, you may end up with imprecise results.
Synonym for DECIMAL.
Storageprecision + 2 bytes DescriptionStores floating-point numbers where precision is critical, such as for monetary values. DECIMAL types require you to specify the precision and scale. The precision is the number of significant digits in the value. The scale is the number of those digits that come after the decimal point. For example, a BALANCE column declared as DECIMAL(9, 2) would store numbers with nine significant digits, two of which are to the right of the decimal point. The range for this declaration would be -9,999,999.99 to 9,999,999.99. If you specify a number with more decimal points, it is rounded to fit the proper scale. Values beyond the range of the DECIMAL are clipped to fit within the range. MySQL actually stores DECIMAL values as strings, not as floating-point numbers. It uses one character for each digit, one character for the decimal points when the scale is greater than 0, and one character for the sign of negative numbers. When the scale is 0, the value contains no fractional part. Prior to MySQL 3.23, the precision actually had to include space for the decimal and sign. This requirement is no longer in place, in accordance with the ANSI specification. ANSI SQL supports the omission of precision and/or scale where the omission of scale creates a default scale of zero and the omission of precision defaults to an implementation-specific value. In the case of MySQL, the default precision is 10.
Storage8 bytes DescriptionA double-precision floating-point number. This type stores large floating-point values. DOUBLE columns store negative values from -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and positive numbers from 2.2250738585072014E-308 to 1.7976931348623157E+308.
Synonym for DOUBLE.
Storage4 bytes DescriptionA single-precision floating-point number. This type is used to store small floating-point numbers. FLOAT columns can store negative values between -3.402823466E+38 and -1.175494351E-38, 0, and positive values between 1.175494351E-38 and 3.402823466E+38.
Storage4 bytes DescriptionA basic whole number with a range of -2,147,483,648 to 2,147,483,647 (0 to 4,294,967,295 unsigned).
Synonym for INT.
Storage3 bytes DescriptionA basic whole number with a range of -8,388,608 to 8,388,607 (0 to 16,777,215 unsigned).
Synonym for DECIMAL.
Synonym for DOUBLE.
Storage2 bytes DescriptionA basic whole number with a range of -32,768 to 32,767 (0 to 65,535 unsigned).
Storage1 byte DescriptionA basic whole number with a range of -128 to 127 (0 to 255 unsigned). 1.4.2 StringsString data types store various kinds of text data. There are several types to accommodate data of different sizes. For each size, there is a type that sorts and compares entries in a case-insensitive fashion in accordance with the sorting rules for the default character set. A corresponding binary type performs simple byte-by-byte sorts and comparisons. In other words, binary values are case sensitive. For CHAR and VARCHAR, the binary types are declared using the BINARY attribute. The TEXT types, however, have corresponding BLOB types as their binary counterparts.
Binary form of TEXT.
SizeSpecified by the size value in a range of to 255 (1 to 255 prior to MySQL 3.23) Storagesize bytes DescriptionA fixed-length text field. String values with fewer characters than the column's size will be right padded with spaces. The right padding is removed on retrieval of the value from the database. CHAR(0) fields are useful for backward compatibility with legacy systems that no longer store values in the column.
Synonym for CHAR.
Synonym for VARCHAR.
Binary form of LONGTEXT.
Size0 to 4,294,967,295 StorageLength of value + 4 bytes DescriptionStorage for large text values. While the theoretical limit on the size of the text that can be stored in a LONGTEXT column exceeds 4 GB, the practical limit is much less due to limitations of the MySQL communication protocol and the amount of memory available to both the client and server ends of the communication.
Binary form of MEDIUMTEXT.
Size0 to 16,777,215 StorageLength of value + 3 bytes DescriptionStorage for medium-sized text values.
Synonym of CHAR.
Synonym of CHAR.
Synonym of CHAR.
Synonym of VARCHAR.
Size0 to 65,535 StorageLength of value + 2 bytes DescriptionStorage for most text values.
Binary form of TINYTEXT.
Size0 to 255 StorageLength of value + 1 byte DescriptionStorage for short text values.
SizeSpecified by the size value in a range of to 255 (1 to 255 prior to MySQL 3.23) StorageLength of value + 1 byte DescriptionStorage for variable-length text. Trailing spaces are removed from VARCHAR values. 1.4.3 DatesMySQL date types are extremely flexible tools for storing date information. They are also extremely forgiving in the belief that it is up to the application, not the database, to validate date values. MySQL only checks that months range from 0 to 12 and dates range from to 31. February 31, 2001, is therefore a legal MySQL date. More useful, however, is the fact that February 0, 2001, is a legal date. In other words, you can use 0 to signify dates in which you do not know a particular piece of the date. Though MySQL is somewhat forgiving on the input format, you should attempt to format all date values in your applications in MySQL's native format to avoid any confusion. MySQL always expects the year to be the left-most element of a date format. If you assign an illegal value in an SQL operation, MySQL inserts a zero for that value. MySQL automatically converts date and time values to integer values when used in an integer context.
FormatYYYY-MM-DD (2001-01-01) Storage3 bytes DescriptionStores a date in the range of January 1, 1000 ('1000-01-01') to December 31, 9999 ('9999-12-31') in the Gregorian calendar.
FormatYYYY-MM-DD hh:mm:ss (2001-01-01 01:00:00) Storage8 bytes DescriptionStores a specific time in the range of 12:00:00 AM, January 1, 1000 ('1000-01-01 00:00:00') to 11:59:59 P.M., December 31, 9999 ('9999-12-31 23:59:59') in the Gregorian calendar.
Formathh:mm:ss (06:00:00) Storage3 bytes DescriptionStores a time value in the range of midnight ('00:00:00') to one second before midnight ('23:59:59').
FormatYYYYMMDDhhmmss (20010101060000) Storage4 bytes DescriptionA simple representation of a point in time down to the second in the range of midnight on January 1, 1970, to one minute before midnight on December 31, 2037. Its primary utility is keeping track of table modifications. When you insert a NULL value into a TIMESTAMP column, the current date and time are inserted instead. When you modify any value in a row with a TIMESTAMP column, the first TIMESTAMP column will be automatically updated with the current date and time.
FormatYYYY (2001) Storage1 byte DescriptionStores a year of the Gregorian calendar. The size parameter enables you to store dates using 2 digit years or 4 digit years. The range for a YEAR(4) is 1900 to 2155; the range for a YEAR(2) is 1970-2069.The default size is YEAR(4). 1.4.4 Complex TypesMySQL's complex data types ENUM and SET are just special string types. We list them separately because they are conceptually more complex and represent a lead into the SQL3 data types that MySQL may support in the future.
Storage1-255 members: 1 byte 256-65,535 members: 2 bytes DescriptionStores one value of a predefined list of possible strings. When you create an ENUM column, you provide a list of all possible values. Inserts and updates are allowed to set the column to values only from that list. Any attempt to insert a value that is not part of the enumeration will cause an empty string to be stored instead. You may reference the list of possible values by index where the index of the first possible value is 0. For example: SELECT COLID FROM TBL WHERE COLENUM = 0; Assuming COLID is a primary key column and COLENUM is the column of type ENUM, this SQL will retrieve the primary keys of all rows in which the COLENUM value equals the first value of that list. Similarly, sorting on ENUM columns happens according to index, not string value. The maximum number of elements allowed for an ENUM column is 65,535.
Storage1-8 members: 1 byte 9-16 members: 2 bytes 17-24 members: 3 bytes 25-32 members: 4 bytes 33-64 members: 8 bytes DescriptionA list of values taken from a predefined set of values. A field can contain any number—including none—of the strings specified in the SET statement. A SET is basically an ENUM that allows each field to contain more than one of the specified values. A SET, however, is not stored according to index, but as a complex bit map. Given a SET with the members Orange, Apple, Pear, and Banana, each element is represented by an "on" bit in a byte, as shown Table 1-2.
In this example, the values Orange and Pear are stored in the database as 5 (0101). You can store a maximum of 64 values in a SET column. Though you can assign the same value multiple times in an SQL statement updating a SET column, only a single value will actually be stored. |
| [ Team LiB ] |
|