MySQL Data Types

DRAFT, 8/24/01 Chapter 17 MySQL supports kinds of data. T description of th data type, the sy syntax. The follo BIGINT[(disp This indicates th italics indicate t your own value BIGINT BIGINT(20) Like the BIGINT a display size. U and 255. Table 17-1 lists complex. You c ate section of th Table 17-1. . MySQ Data Type BIGINT BLOB CHAR CHARACTER CHARACTER VAwing example shows how BIGI lay_size)] at you can use BIGINT alone hat you do not enter displa in there. Thus possible uses of type above, many MySQL dat nless otherwise specified, this v the data types and categorizes an then find the full descriptio is chapter. L Data Types. Class Num String String String RYING StringCopyright © 2001 O’Reiification erica types support the specification of alue must be an integer between 1 them as numeric, string, date, or n of the data type in the appropri-17 17.MySQL Data Types a wide variety of data types to support the storage of different his chapter lists the full range of these data types as well as a eir functionality, syntax, and data storage requirements. For each ntax shown uses square brackets ([]) to indicate optional parts of the NT is explained in this chapter: or with a display size value. The y_size literally, but instead place BIGINT include:335 lly & Associates, Inc. DRAFT, 8/24/01 In some cases, M creation to some • VARCHAR -> characters, i DATE DATETIME DEC DECIMAL DOUBLE DOUBLE PRECI ENUM FLOAT INT INTEGER LONGBLOB LONGTEXT MEDIUMBLOB MEDIUMINT MEDIUMTEXT NCHAR NATIONAL CHA NATIONAL CHA NATIONAL VAR NUMERIC REAL SET SMALLINT TEXT TIME TIMESTAMP TINYBLOB TINYINT TINYTEXT VARCHAR YEAR Table 17-1. . MySQ Data TypeySQL silently changes the column type you specify in your table thing else. These cases are: CHAR: When the specified VARCHAR column size is less than 4 t is converted to CHAR. Date Date Numeric Numeric Numeric SION Numeric Complex Numeric Numeric Numeric String String String Numeric String String R String RACTER String CHAR String Numeric Numeric Complex Numeric String Date Date String Numeric String String Date L Data Types. ClassificationCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 • CHAR -> VA all CHAR co VARCHAR. • TIMESTAMP even value b vert the field verted to the Numeric MySQL supports down into two types differ bas types allow you point. In such c greater than two than two less tha two greater than When you inser type allows, it w values) or the m issue a warning INFILE, UPDAT The AUTO_INCR integer type in a either a primary insert into a tabl (or specify a NU mum value will The UNSIGNED umn may contai The ZEROFILL zeroes when dis the column’s disRCHAR: When a table has at least one column of a variable length, lumns greater than three characters in length are converted to display sizes: Display sizes for TIMESTAMP fields must be an etween 2 and 14. A display size of 0 or greater than 14 will con- to a display size of 14. An odd-valued display size will be con- next highest even value. Data Types all ANSI SQL2 numeric data types. MySQL numeric types break groups: integer and floating point types. Within each group, the ically by the amount of storage required for them. The floating to optionally specify the number of digits that follow the decimal ases, the digits value should be an integer from 0 to 30 and no less than the display size. If you do make the digits value greater n the display size, the display size will automatically change to be the digits value. t a value into a column that requires more storage than the data ill be clipped to the minimum value for that data type (negative aximum value for that data type (positive values). MySQL will when such clipping occurs during ALTER TABLE, LOAD DATA E and multi-row INSERT statements. EMENT attribute may be supplied for at most one column of an table. In addition to being an integer type, the column must be key or the sole column in a unique index. When you attempt an e with such an integer field and fail to specify a value for that field LL value), a value of one greater than the column’s current maxi- be automatically inserted. attribute may be used with any numeric type. An unsigned col- n only positive integers or floating point values. attribute indicates that the column should be left padded with played by MySQL. The number of zeroes padded is determined by play width.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 BIGINT Syntax BIGINT[(d [ZEROFILL Storage 8 bytes Description Largest integer 9,223,372,036,85 18,446,744,073,7 form arithmetic BIGINT or DOU metic operati 9,223,372,036,85 DEC Synonym for DE DECIMAL Syntax DECIMAL[( Storage precision + 2 Description Stores floating p ues. DECIMAL t is the number o digits that come MAL(9, 2) wo to the right of 9,999,999.99 to 9 is rounded so it clipped to fit wit MySQL actually uses one characisplay_size)] [AUTO_INCREMENT] [UNSIGNED] ] type supporting the range of whole numbers from - 4,775,808 to 9,223,372,036,854,775,807 (0 to 09,551,615 unsigned). BIGINT has some issues when you per- on unsigned values. MySQL performs all arithmetic using signed BLE values. You should therefore avoid performing any arith- ons on unsigned BIGINT values greater than 4,775,807. If you do, you may end up with imprecise results. CIMAL. precision, [scale])] [ZEROFILL] bytes oint numbers where precision is critical, such as for monetary val- ypes require you to specify the precision and scale. The precision f significant digits in the value. The scale is the number of those after the decimal point. A BALANCE column declared as DECI- uld thus store numbers with 9 significant digits, two of which are the decimal point. The range for this declaration would be - ,999,999.99. If you specify a number with more decimal points, it fits the proper scale. Values beyond the range of the DECIMAL are hin the range. stores DECIMAL values as strings, not floating point numbers. It ter for each digit as well as one character for the decimal pointsCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 when the scale bers. When the 23, the precisio requirement is n ANSI SQL suppo scale creates a d implementation- DOUBLE Syntax DOUBLE[(d Storage 8 bytes Description A double-precis large floating po 79769313486231 between 2.22507 DOUBLE PRE Synonym for DO FLOAT Syntax FLOAT[(di Storage 4 bytes Description A single-precisio floating point n 402823466E+38 175494351E-38 ais greater than 0 and one character for the sign of negative num- scale is 0, the value contains no fractional part. Prior to MySQL 3. n actually had to include space for the decimal and sign. This o longer in place in accordance with the ANSI specification. rts the omission of precision and/or scale where the omission of efault scale of zero and the omission of precision defaults to an specific value. In the case of MySQL, the default precision is 10. isplay_size, digits)] [ZEROFILL] ion floating point number. This type is used for the storage of int values. DOUBLE columns can store negative values between -1. 57E+308 and -2.2250738585072014E-308, 0, and positive numbers 38585072014E-308 and 1.7976931348623157E+308. CISION UBLE. splay_size, digits)] [ZEROFILL] n floating point number. This type is used for the storage of small umbers. FLOAT columns can store negative values between -3. and -1.175494351E-38, 0, and positive values between 1. nd 3.402823466E+38.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 INT Syntax INT[(disp [ZEROFILL Storage 4 bytes Description A basic whole 4,294,967,295 un INTEGER Synonym for IN MEDIUMINT Syntax MEDIUMINT [ZEROFILL Storage 3 bytes Description A basic whole n unsigned). NUMERIC Synonym for DE REAL Synonym for DOlay_size)] [AUTO_INCREMENT] [UNSIGNED] ] number with a range of -2,147,483,648 to 2,147,483,647 (0 to signed). T. [(display_size)] [AUTO_INCREMENT] [UNSIGNED] ] umber with a range of -8,388,608 to 8,388,607 (0 to 16,777,215 CIMAL. UBLE.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 SMALLINT Syntax SMALLINT[ [ZEROFILL Storage 2 bytes Description A basic whole n TINYINT Syntax TINYINT[( [ZEROFILL Storage 1 byte Description A basic whole n String Da String data type sorted and comp rules for the de type. For CHAR attribute. All of t binary counterpa Text fields are c with the default not interpreted byte-by-byte bas case-sensitive. BLOB Binary form of T(display_size)] [AUTO_INCREMENT] [UNSIGNED] ] umber with a range of -32,768 to 32,767 (0 to 65,535 unsigned). display_size)] [AUTO_INCREMENT] [UNSIGNED] ] umber with a range of -128 to 127 (0 to 255 unsigned). ta Types s store various kinds of text data. By default, string columns are ared in a case-insensitive fashion in accordance with the sorting fault character set. Each string type has a corresponding binary and VARCHAR, the binary types are declared using the BINARY he TEXT types, however, have corresponding BLOB types as their rts. ompared and sorted in a case-insensitive fashion in accordance character set of the server. Binary fields, on the other hand, are according to a character set. Comparisons and sorts occur on a is without interpretation. In other words, binary values are treated EXT.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 CHAR Syntax CHAR(size Size Specified by 23). Storage size bytes Description A fixed-length te will be right-pad value from the d CHAR(0) fields longer store valu CHARACTER Synonym for CH CHARACTER Synonym for VA LONGBLOB Binary form of L LONGTEXT Syntax LONGTEXT Size 0 to 4,294,96 Storage length of va) [BINARY] the size value in a range of 0 to 255 (1 to 255 prior to MySQL 3. xt field. String values with fewer characters than the column’s size ded with spaces. The right-padding is removed on retrieval of the atabase. are useful for backwards compatibility with legacy systems that no es in the column or for binary values (NULL vs. ’’). AR. VARYING RCHAR. ONGTEXT. 7,295 lue + 4 bytesCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Description Storage for large While the theor TEXT column e the MySQL com the client and se MEDIUMBLOB Binary form of M MEDIUMTEXT Syntax MEDIUMTEX Size 0 to 16,777,2 Storage length of va Description Storage for medi NCHAR Synonym of CHA NATIONAL C Synonym of CHA NATIONAL C Synonym of CHA NATIONAL V Synonym of VAR text values. etical limit to the size of the text that can be stored in a LONG- xceeds 4GB, the practical limit is much less due to limitations of munication protocol and the amount of memory available on both rver ends of the communication. EDIUMTEXT. T 15 lue + 3 bytes um-sized text values. R. HAR R. HARACTER R. ARCHAR CHAR.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 TEXT Syntax TEXT Size 0 to 65,535 Storage length of va Description Storage for most TINYBLOB Binary form of T TINYTEXT Syntax TINYTEXT Size 0 to 255 Storage length of va Description Storage for short VARCHAR Syntax VARCHAR(s Size Specified by 23). Storage length of value + 2 bytes text values. INYTEXT. lue + 1 byte text values. ize) [BINARY] the size value in a range of 0 to 255 (1 to 255 prior to MySQL 3. lue + 1 byteCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Description Storage for varia ues when stored Date Dat MySQL date typ are also extreme database, to vali of 0-12 and date MySQL date. Mo In other words, lar piece of the d Though MySQL should actually a any confusion. date format. If zeroes for that v MySQL will also values when use DATE Syntax DATE Format YYYY-MM-D Storage 3 bytes Description Stores a date in 9999 (’9999-12 DATETIME Syntax DATETIMEble-length text. Trailing spaces are removed from VARCHAR val- in the database in conflict with the ANSI specification. a Types es are extremely flexible tools for storing date information. They ly forgiving in the belief that it is up to the application, not the date date values. MySQL only checks that months are in the range s are in the range of 0-31. February 31, 2001 is therefore a legal re useful, however, is the fact that February 0, 2001 is a legal date. you can use 0 to signify dates where you do not know a particu- ate. is somewhat forgiving on the input format, your applications ttempt to format all date values in MySQL’s native format to avoid MySQL always expects the year to be the left-most element of a you assign illegal values in a SQL operation, MySQL will insert alue. perform automatic conversion of date and time values to integer d in an integer context. D (2001-01-01) the range of January 1, 1000 (’1000-01-01’) to December 31, -31’) in the Gregorian calendar.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Format YYYY-MM-D Storage 8 bytes Description Stores a specific 00:00:00’) to in the Gregorian TIME Syntax TIME Format hh:mm:ss (0 Storage 3 bytes Description Stores a time va midnight (’23:5 TIMESTAMP Syntax TIMESTAMP Format YYYYMMDD Storage 4 bytes Description A simple repres midnight on Jan 2037. Its primary a NULL value i inserted instead.D hh:mm:ss (2001-01-01 01:00:00) time in the range of 12:00:00 AM, January 1, 1000 (’1000-01-01 11:59:59 PM, December 31, 9999 (’9999-12-31 23:59:59’) calendar. 6:00:00) lue in the range of midnight (’00:00:00’) to 1 second before 9:59’). [(display_size)] hhmmss (20010101060000) entation of a point in time down to the second in the range of uary 1, 1970 to one minute before midnight on December 31, utility is in keeping track of table modifications. When you insert nto a TIMESTAMP column, the current date and time will be When you modify any value in a row with a TIMESTAMP col-Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 umn, the first TI date and time. YEAR Syntax YEAR Format YYYY (2001 Storage 1 byte Description Stores a year of Complex MySQL’s comple string types. We represent a lead ENUM Syntax ENUM(valu Storage 1-255 memb 256-65,535 m Description Stores one valu ENUM column, then allowed to a value that is n instead. You may referen possible value is SELECT COLIDMESTAMP column will automatically be updated with the current ) the Gregorian calendar in the range of 1900 to 2155. Data Types x data types ENUM and SET are really nothing more than special break them out because they are conceptually more complex and into the SQL3 data types that MySQL may one day support. e1, value2, ...) ers: 1 byte embers: 2 bytes e of a predefined list of possible strings. When you create an you provide a list of all possible values. Inserts and updates are set the column to values only from that list. Any attempt to insert ot part of the enumeration will cause an empty string to be stored ce the list of possible values by index where the index of the first 0. For example: FROM TBL WHERE COLENUM = 0;Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Assuming COLI ENUM, this SQL equals the first according to ind The maximum n SET Syntax SET(value Storage 1-8 member 9-16 membe 17-24 memb 25-32 memb 33-64 memb Description A list of values t that allows mult instead as a com "Pear", and "Ban shown in Table The values "Oran You can store a the same value single value will Table 17-2. . MySQ Member Orange Apple Pear BananaD is a primary key column and COLENUM is the column of type will retrieve the primary keys of all rows with COLENUM value value of that list. Similarly, sorting on ENUM columns happens ex, not string value. umber of elements allowed for an ENUM column is 65,535. 1, value2, ...) s: 1 byte rs: 2 bytes ers: 3 bytes ers: 4 bytes ers: 8 bytes aken from a pre-defined set of values. A SET is basically an ENUM iple values. A SET, however, is not stored according to index but plex bit map. Given a SET with the members "Orange", "Apple", ana", each element is represented by an "on" bit in a byte as 17-2 ge" and "Pear" are therefore stored in the database as 5 (0101). maximum of 64 values in a SET column. Though you can assign multiple times in a SQL statement updating a SET column, only a actually be stored. L Representations of Set Elements Decimal Value Bitwise Representation 1 0001 2 0010 4 0100 8 1000Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01Copyright © 2001 O’Reilly & Associates, Inc.