MySQL Administrator's Bible- P5

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

146
lượt xem 32
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Với tập trung đặc biệt vào việc phát hành lớn tiếp theo của MySQL, nguồn tài nguyên này cung cấp một khuôn khổ vững chắc cho bất cứ ai mới vào MySQL hoặc chuyển MySQL Administrator's Bible- P5: từ một nền tảng cơ sở dữ liệu, cũng như các quản trị MySQL kinh nghiệm. Các bộ đôi tác giả cao hồ sơ cung cấp bảo hiểm thiết yếu của các nguyên tắc cơ bản của phạm vi bảo hiểm cơ sở dữ liệu quản lý, bao gồm cả cách tiếp cận độc đáo MySQL của các tính năng cơ sở...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: MySQL Administrator's Bible- P5

MySQL Data Types 5 TABLE 5-1 Summary of MySQL Character String Types SQL Fixed/Variable Data Type Name Standard? Length Range Size Attributes CHAR Yes Fixed Length of M*x bytes ASCII 0–255, BINARY depends CHARACTER on character SETCOLLATION set DEFAULT UNICODE VARCHAR Yes Variable Length of L*x+1 ASCII 0–255, if L255 SETCOLLATION set DEFAULT UNICODE TINYTEXT No Variable Max length of L+1 bytes ASCII 255 bytes 1 byte stores BINARY length CHARACTER SETCOLLATION UNICODE TEXT No Variable Max length of L+2 bytes ASCII 65,535 bytes 2 bytes store BINARY (64 Kb) length CHARACTER SETCOLLATION UNICODE MEDIUMTEXT No Variable Max length of L+3 bytes ASCII 16,777,215 3 bytes store BINARY bytes (16 Mb) length CHARACTER SETCOLLATION UNICODE LONGTEXT No Variable Max length of L+4 bytes ASCII 4,294,967,295 2 bytes store BINARY bytes (4 Gb) length CHARACTER SETCOLLATION NOT NULL NULL UNICODE 167
Part II Developing with MySQL Like character string types, MySQL supports the SQL standard for ﬁxed- and variable-length strings, but not for character objects. The SQL standard states that the NATIONAL equivalents of character string types are the same as the character string types, except that a speciﬁc character set is used. In MySQL, this character set is utf8: mysql> CREATE TABLE nchar_test (nchar_fld NCHAR(10)); Query OK, 0 rows affected (0.55 sec) mysql> SHOW CREATE TABLE nchar_test\G *************************** 1. row *************************** Table: nchar_test Create Table: CREATE TABLE `nchar_test` ( `nchar_fld` char(10) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The characteristics and usage of national character string types is exactly the same as character string types, with one exception: the ASCII and UNICODE attributes are not proper syntax. This is because the ASCII and UNICODE attributes set the character set, which conﬂicts with the NATIONAL keyword. For details on character string types, see the section ‘‘Character String Types’’ earlier in this chapter. Binary Large Object String Types A binary string type is the least restrictive data type. There is one binary large object type in the ISO SQL:2003 standard, with two aliases: ■ BINARY LARGE OBJECT(length) ■ BLOB(length) MySQL supports only the second standard syntax, BLOB(length). However, MySQL extends the SQL standard for binary large object string types with ﬁve additional binary types: ■ TINYBLOB ■ MEDIUMBLOB ■ LONGBLOB ■ BINARY(length) ■ VARBINARY(length) 168
MySQL Data Types 5 Binary string types are byte strings. Character strings are ordered lexically; binary strings are ordered by each byte’s value. The standard does not specify what makes an object ‘‘large,’’ and there is no standard equivalent for smaller binary strings, so we have included the smaller BINARY and VARBINARY byte string types into this category. BLOB values The four BLOB types are very similar to each other — the only differences are the maximum amount of data each can store and the overhead involved in storing the size of each record: ■ TINYBLOB — Up to 255 bytes, 1 byte overhead ■ BLOB — Up to 64 Kb, 2 bytes overhead ■ MEDIUMBLOB — Up to 16 Mb, 3 bytes overhead ■ LONGBLOB — Up to 4 Gb, 4 bytes overhead A BLOB data type ﬁeld is a separately allocated object than the table that contains it, like the TEXT data type ﬁelds. BINARY values BINARY and VARBINARY are similar to the CHAR and VARCHAR data types, respectively. For the BINARY and VARBINARY data types, the length is an integer representing the length, in bytes, of a string. A data type of BINARY or VARBINARY with a length of 0 is valid, but can hold only two strings: the empty string and NULL. Note that BINARY and VARBINARY are different from CHAR BINARY and VARCHAR BINARY — BINARY and VARBINARY are byte strings, and CHAR BINARY and VARCHAR BINARY are case-sensitive character strings. BINARY length The length of BINARY is an integer from 0–255. If a string is stored as a BINARY and is smaller than the length, binary spaces (represented by \0) are appended to the string. A binary space is different from a regular space character; a binary space has an ASCII value of 0 and the regular space character has an ASCII value of 32: mysql> SELECT ’ ’,ASCII(’ ’), ’\0’, ASCII(’\0’); +---+------------+---+-------------+ | | ASCII(’ ’) | | ASCII(’\0’) | +---+------------+---+-------------+ | | 32 | | 0 | +---+------------+---+-------------+ 1 row in set (0.02 sec) Because of this, a value of ’a’ appears before a value of ’a ’ in an ascending sort. This also means that the BINARY value ’a’ is the same as the BINARY value ’a\0’ for the purpose of unique constraints. There is no removal of trailing spaces when a BINARY string is retrieved from a table. 169
Part II Developing with MySQL VARBINARY length The maximum length of a VARBINARY is restricted only by the maximum row length. In most storage engines, the maximum row length is the maximum allowed by MySQL, which is 65,535 bytes. Only the NDB storage engine has a different maximum value. Like a VARCHAR, in theory, the maximum length of a VARBINARY is 65,535 bytes. In practice, there is some overhead in storing the VARBINARY data type, which further limits the actual possible size of a VARBINARY. If the length of VARBINARY is less than 255 bytes, one byte per row is used to store the actual length of the string. If the length of VARBINARY is greater than 255 bytes, the overhead cost of storing the string length is two bytes per row. There is also per-table overhead — every table allocates one byte for every set of eight potentially nullable ﬁelds, regardless of ﬁeld types. Thus, the maximum length of a VARBINARY is 65,532 bytes, and that is only if the VARBINARY ﬁeld is the only ﬁeld in the table. For example, another ﬁeld with a type of INT uses 4 bytes, so the maximum length of a VARBINARY in that table would be 65,528 bytes. For VARBINARY strings larger than the maximum allowed, use the BLOB data type. If you try to deﬁne a table that exceeds the maximum row length, you will get the following error: mysql> CREATE TABLE max_len_varbin(fld VARBINARY(65533)); ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs Table 5-2 shows a summary of the MySQL binary data types. As with the character string data types, the numbers given in Table 5-2 are the basic storage requirements of MySQL. The storage engine used may add additional overhead or provide data compression that reduces the storage required. See Chapter 11 for more details about storage engines. Numeric Types The ISO SQL:2003 standard deﬁnes two numeric types. Each numeric type has a few different data types. The standard numeric types and their associated keywords are: ■ Exact numeric type: ■ NUMERIC(g,f) ■ DECIMAL(g,f) can be abbreviated as DEC ■ SMALLINT ■ INTEGER can be abbreviated as INT ■ BIGINT 170
MySQL Data Types 5 TABLE 5-2 Summary of MySQL Binary Data Types SQL Fixed/Variable Data Type Name Standard? Length Range Size Attributes BINARY No Fixed Length of M bytes DEFAULT 0–255 bytes NOT NULL NULL VARBINARY No Variable Length of L*x+1 DEFAULT 0–65,532 bytes if L255 TINYBLOB No Variable Max length of L+1 bytes NOT NULL 255 bytes 1 byte stores NULL length BLOB No Variable Max length of L+2 bytes NOT NULL 65,535 bytes 2 bytes store NULL (64 Kb) length MEDIUMBLOB No Variable Max length of L+3 bytes NOT NULL 16,777,215 3 bytes store NULL bytes (16 Mb) length LONGBLOB No Variable Max length of L+4 bytes NOT NULL 4,294,967,295 2 bytes store NULL bytes (4 Gb) length ■ Approximate numeric type: ■ FLOAT(p) ■ REAL ■ DOUBLE PRECISION MySQL supports these data types with one exception — the DOUBLE PRECISION data type is simply named DOUBLE. In addition, the NUMERIC data type is an alias for the DECIMAL data type. The standard SQL has been extended to add these additional numeric data types: ■ Exact numeric types: ■ TINYINT ■ MEDIUMINT ■ BIT(x) ■ SERIAL In MySQL, the SERIAL numeric data type is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE KEY. 171
Part II Developing with MySQL By default, the REAL numeric data type is an alias for DOUBLE. However, you can change that behavior by changing the sql_mode to include REAL_AS_FLOAT, which causes the REAL numeric data type to be an alias for FLOAT. See ‘‘Choosing SQL Modes’’ later in this chapter for more detail. Numeric data sizes and ranges Each numeric data type can store a limited range of values, and each numeric data type stores its values in a certain size. DECIMAL size and range A DECIMAL ﬁeld is deﬁned using the syntax DECIMAL(g,f). The ﬁrst argument (g) is the total number of digits, and the second argument (f) is the number of digits after the decimal point. For example, the data type DECIMAL(5,2) can store values between –999.99 and 999.99. The default value for g is 10 and the default value for f is 0; the maximum value for g is 65 and the maximum value for f is 30. The size of a DECIMAL ﬁeld is variable. MySQL stores DECIMAL in a binary format, where each group of 9 digits is stored in 4 bytes. The size of a DECIMAL ﬁeld is determined by the num- ber of digits in the integer part (the value of p-s) and the number of digits in the fractional part (the value of s). The integer and fractional parts are stored separately in 4-byte, 9-digit groups. If the number of digits in each group is not divisible by nine, the remaining digits are stored in CEILING(digits/2) bytes. As an example, the size of DECIMAL(12,2) can be calculated as follows: Integer part = (12-2) digits = 10 digits = 9 digits + 1 digit 9 digits = 4 bytes 1 digit left over CEILING(1/2) = 1 byte Total integer part = 5 bytes Fractional part = 2 digits CEILING(2/2) = 1 byte Total size = 6 bytes Integer sizes and ranges The integer data types are TINYINT, SMALLINT, INT, MEDIUMINT, and BIGINT. Table 5-3 shows the data sizes and ranges for the integer data types: Note that the size of the ﬁeld is the size of the data type, not the size of the value stored. For example, the value 123 stored in a BIGINT ﬁeld is stored in 8 bytes. The same value 123 stored in a TINYINT ﬁeld is stored in 1 byte. 172
MySQL Data Types 5 TABLE 5-3 Data Sizes and Ranges for Integer Data Types Data Type SIGNED Range UNSIGNED Range Size TINYINT –128 to 127 0 to 255 1 byte SMALLINT –32,768 to 32,767 0 to 65,535 2 bytes MEDIUMINT –8,388,608 to 8,388,607 0 to 16,777,215 3 bytes INT –2,147,483,648 to 0 to 4,294,967,295 4 bytes 2,147,483,647 BIGINT –9,223,372,036,854,775,808 to 0 to 8 bytes 9,223,372,036,854,775,807 18,446,744,073,709,551,615 MySQL allows a minimum display width to be set for integer types. If an integer value is less than this width, the value will be left-padded with enough spaces so the value is displayed as this width. This is only for the display and does not change the actual value returned. This can be speciﬁed by giving the width as an argument to the integer data type, for example INT(4). This does not change the range nor the size of the data type, just the minimum display width. MySQL performs calculations and comparisons using double-precision ﬂoating-point numbers. Calculations using unsigned BIGINT values larger than 63 bits (9,223,372,036,854,775,807) should only be done via bit functions. BIT size and range The BIT data type stores integers as a series of bits. The range of a BIT ﬁeld is determined by the argument to BIT(x). The default range is 1 bit and the range can be set from 1 to 64 bits. The BIT values are stored in binary format (that is, it is stored in base 2, as opposed to deci- mal format, which is stored in base 10). Unlike other data types, a BIT value needs to be con- verted upon retrieval to produce a human-readable result. How to retrieve BIT values depends on whether you want to retrieve integers or bit strings: mysql> USE test; Database changed mysql> CREATE TABLE bit_test (bt BIT(10)); Query OK, 0 rows affected (0.64 sec) mysql> INSERT INTO bit_test (bt) VALUES (0),(1),(2),(3),(4); Query OK, 5 rows affected (11.78 sec) Records: 5 Duplicates: 0 Warnings: 0 173
Part II Developing with MySQL mysql> SELECT bt,bt+0,BIN(bt) FROM bit_test; +------+------+---------+ | bt | bt+0 | BIN(bt) | +------+------+---------+ | | 0 | 0 | | | 1 | 1 | | | 2 | 10 | | ♥ | 3 | 11 | | ♦ | 4 | 100 | +------+------+---------+ 5 rows in set (0.09 sec) The BIT value, as it is stored, is shown in the ﬁrst ﬁeld of the result. As you can see, it is not in human-readable format. The second ﬁeld of the result casts the result as an integer, and the third ﬁeld casts the result as a bit string. FLOAT size and range The FLOAT data type is a single-precision ﬂoating-point number. Floating-point means that unlike the DECIMAL data type, the decimal point can be anywhere in the number — the decimal point ﬂoats. A FLOAT is limited in how many signiﬁcant digits it can store. In the SQL standard, this limitation can be speciﬁed as the argument p (p stands for precision). In MySQL, this limitation depends on the hardware and operating system, but is usually a precision of 24 bits. This translates to 6 or 7 signiﬁcant digits, and a storage cost of 4 bytes per FLOAT. If a FLOAT ﬁeld is deﬁned with a larger value of p, it is changed into a DOUBLE ﬁeld: mysql> USE test; Database changed mysql> CREATE TABLE float_double_test ( -> f1 FLOAT(1), f2 FLOAT(10), f3 FLOAT(23), -> f4 FLOAT(24), f5 FLOAT(53)); Query OK, 0 rows affected (0.16 sec) mysql> SHOW CREATE TABLE float_double_test\G *************************** 1. row *************************** Table: float_double_test Create Table: CREATE TABLE `float_double_test` ( `f1` float DEFAULT NULL, `f2` float DEFAULT NULL, `f3` float DEFAULT NULL, `f4` float DEFAULT NULL, `f5` double DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.06 sec) mysql> ALTER TABLE float_double_test ADD COLUMN f6 FLOAT(54); ERROR 1063 (42000): Incorrect column specifier for column ’f6’ 174
MySQL Data Types 5 A signiﬁcant digit is a digit that signiﬁes precision, and not a power of ten. The following example was done on a system where the number of signiﬁcant digits is six: mysql> CREATE TABLE float_test (ft float, ft_text varchar(10)); Query OK, 0 rows affected (0.59 sec) mysql> INSERT INTO float_test (ft,ft_text) VALUES -> (1234567,’1234567’), (123456,’123456’), (12345.6,’12345.6’), -> (123.456,’123.456’), (1.23456,’1.23456’), -> (0.00123456,’0.00123456’), -> (1.23456e-3,’1.23456e-3’), (123456000,’123456000’), -> (1.23456e8,’1.23456e8’), (123456e3,’123456e3’); Query OK, 10 rows affected (0.08 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> SELECT ft, ft_text FROM float_test ORDER BY ft; +------------+------------+ | ft | ft_text | +------------+------------+ | 0.00123456 | 0.00123456 | | 0.00123456 | 1.23456e-3 | | 1.23456 | 1.23456 | | 123.456 | 123.456 | | 12345.6 | 12345.6 | | 123456 | 123456 | | 1234570 | 1234567 | | 123456000 | 123456000 | | 123456000 | 1.23456e8 | | 123456000 | 123456e3 | +------------+------------+ 10 rows in set (0.06 sec) Note that the values 123456000, 1.23456e8 and 123456e3 are all the same ﬂoating-point number. The numbers including e indicate scientiﬁc notation, replacing e with *10 ˆ . Indeed, 123456000, 1.23456*10 ˆ 8 and 123456*10 ˆ 3 all signify the same number. MySQL automatically rounded the value with more than six signiﬁcant digits to have exactly six signiﬁcant digits — the value 1234567 was rounded to 1234570. As mentioned previously, MySQL supports the SQL standard FLOAT(p) syntax. It also supports a syntax similar to the DECIMAL syntax. A FLOAT ﬁeld can be deﬁned as in the preceding example, or it can be deﬁned as FLOAT(g,f). The ﬁrst argument (g) is the total number of digits, and the second argument (f) is the number of digits after the decimal point. The FLOAT(g,f) syntax can be used to override the default amount of signiﬁcant digits. Therefore, higher or lower precision can be speciﬁed using the two-argument syntax. It should be noted that this is not an exact substitute for deﬁning precision, because the number of digits after the decimal point is ﬁxed. 175
Part II Developing with MySQL MySQL performs calculations and comparisons using double-precision ﬂoating-point numbers. Queries involving a FLOAT ﬁeld may return unexpected results; rounding an (internal) DOUBLE number often does not yield the same result as rounding a FLOAT number. Therefore, comparing a FLOAT ﬁeld to a calculated number will often produce incorrect results, because the calculated number is a DOUBLE. The following example shows that calculating a FLOAT value (by adding 0) changes it to a DOUBLE value, which can produce a very different value from the original FLOAT: mysql> SELECT ft_text, ft, ft+0 FROM float_test; +------------+------------+---------------------+ | ft_text | ft | ft+0 | +------------+------------+---------------------+ | 1234567 | 1234570 | 1234567 | | 123456 | 123456 | 123456 | | 12345.6 | 12345.6 | 12345.599609375 | | 123.456 | 123.456 | 123.45600128173828 | | 1.23456 | 1.23456 | 1.2345600128173828 | | 0.00123456 | 0.00123456 | 0.00123456004075706 | | 1.23456e-3 | 0.00123456 | 0.00123456004075706 | | 123456000 | 123456000 | 123456000 | | 1.23456e8 | 123456000 | 123456000 | | 123456e3 | 123456000 | 123456000 | +------------+------------+---------------------+ 10 rows in set (0.04 sec) DOUBLE size and range The DOUBLE data type is a double-precision ﬂoating-point number. Like a FLOAT, a DOUBLE is limited in how many signiﬁcant digits it can store. See previous subsection ‘‘FLOAT Size and Range’’ for an explanation of ﬂoating-point numbers and signiﬁcant digits. The data type is named DOUBLE because the limitation is approximately double the limitation of a single-precision FLOAT. As with FLOAT, this limitation depends on the hardware and operating system, but is usually a precision of 53 bits. This translates to 14 or 15 signiﬁcant digits, and a storage cost of 8 bytes per DOUBLE. As with FLOAT, MySQL supports a syntax similar to the DECIMAL syntax. A DOUBLE ﬁeld can be deﬁned with no parameters as DOUBLE, or it can be deﬁned as DOUBLE(g,f). The ﬁrst argu- ment (g) is the total number of digits, and the second argument (f) is the number of digits after the decimal point. The DOUBLE(g,f) syntax can be used to override the default number of signiﬁcant digits. It should be noted that this is not an exact substitute for deﬁning precision, because the number of digits after the decimal point is ﬁxed. 176
MySQL Data Types 5 Numeric data type attributes Numeric types can be deﬁned with attributes that affect the data range and how the data is stored and displayed: ■ NOT NULL — By default, NULL values are allowed. To disallow NULL values, use the NOT NULL attribute. See the ‘‘Using NULL Values’’ section later in this chapter for an explana- tion of problems NULL values can cause. ■ NULL — The NULL attribute is shorthand for DEFAULT NULL. See the ‘‘Using NULL Val- ues’’ section later in this chapter for an explanation of problems NULL values can cause. ■ DEFAULT — The DEFAULT attribute is valid for all numeric data types. The DEFAULT attribute causes a numeric data type to have a default value when a value is not speciﬁed. This means that an INSERT statement does not have to include a value for this ﬁeld; if it does not, the value following DEFAULT will be inserted. Valid DEFAULT values include NULL and valid numerical values for the ﬁeld (that is, a ﬁeld speciﬁed as INT DEFAULT 1.9 will be converted to INT DEFAULT 2). Functions are not allowed in a DEFAULT expression. ■ If no DEFAULT value is speciﬁed, MySQL will create numeric data type ﬁelds as DEFAULT NULL. If a ﬁeld does not have a DEFAULT value and NOT NULL is also speciﬁed, there is no DEFAULT value, and INSERT statements must supply a value for that ﬁeld. ■ AUTO_INCREMENT — The AUTO_INCREMENT attribute is used to deﬁne a sequence as a default value for a ﬁeld. All numeric data types support the AUTO_INCREMENT attribute except for BIT and DECIMAL. The AUTO_INCREMENT attribute requires that a UNIQUE index exists on the ﬁeld to ensure the sequence has no duplicates. If AUTO_INCREMENT is speciﬁed on a ﬁeld, the ﬁeld is converted to use the NOT NULL attribute. There can only be one AUTO_INCREMENT ﬁeld per table, and the sequence generated by an AUTO_INCREMENT ﬁeld in one table cannot be used in any other table. By default, the sequence starts at 1 and increases by 1 for every insert. To change the num- ber the sequence starts at, set the auto_increment_offset variable in the conﬁguration ﬁle or set the dynamic session or global variable of the same name. To change the amount the sequence increases by, set the auto_increment_increment variable in the conﬁgu- ration ﬁle or set the dynamic session or global variable of the same name. To see the next AUTO_INCREMENT value for an existing table, query the TABLES table in the INFORMATION_SCHEMA database, or use SHOW CREATE TABLE: mysql> USE INFORMATION_SCHEMA; Database changed mysql> SELECT AUTO_INCREMENT -> FROM TABLES -> WHERE TABLE_SCHEMA=’sakila’ AND TABLE_NAME=’category’\G *************************** 1. row *************************** AUTO_INCREMENT: 17 1 row in set (0.00 sec) 177
Part II Developing with MySQL mysql> USE sakila; Database changed mysql> SHOW CREATE TABLE category\G *************************** 1. row *************************** Table: category Create Table: CREATE TABLE `category` ( `category_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(25) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`category_id`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> SELECT MAX(category_id) FROM category; +------------------+ | MAX(category_id) | +------------------+ | 16 | +------------------+ 1 row in set (0.00 sec) To change the next AUTO_INCREMENT value for an existing table, use the following ALTER TABLE syntax: mysql> ALTER TABLE category AUTO_INCREMENT=20; Query OK, 16 rows affected (0.34 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> SELECT AUTO_INCREMENT -> FROM INFORMATION_SCHEMA.TABLES -> WHERE TABLE_SCHEMA=’sakila’ AND TABLE_NAME=’CATEGORY’\G *************************** 1. row *************************** AUTO_INCREMENT: 20 1 row in set (0.00 sec) AUTO_INCREMENT values can only be 0 or positive. If a table does not contain a ﬁeld with the AUTO_INCREMENT attribute, the AUTO_INCREMENT ﬁeld for that table is NULL. ■ UNSIGNED — The default range of numeric types includes 0, negative numbers, and positive numbers. To change the range to include only 0 and positive numbers, use the UNSIGNED attribute. Integer data types are limited by the amount of storage space, so if the UNSIGNED attribute is speciﬁed, an integer data type can actually hold larger numbers than when negative numbers are allowed (see Table 5-1). Any ﬁeld that allows the UNSIGNED attribute also allows the SIGNED attribute to be spec- iﬁed. However, because that is a default attribute, there is no need to specify SIGNED. In fact, the SIGNED attribute will not appear in the table schema even if it is explicitly speci- ﬁed in the ﬁeld deﬁnition. The only numeric data type that does not allow UNSIGNED is the BIT numeric data type. 178
MySQL Data Types 5 ■ ZEROFILL — The ZEROFILL attribute is used to change the padding of numeric data types from spaces to zeros. It only changes this padding when displaying numbers. The number is still stored in the ﬁeld as usual, and requires no extra bytes to store. The only numeric data type that does not allow ZEROFILL is the BIT numeric data type. If the ZEROFILL attribute is speciﬁed the ﬁeld is automatically converted to use the UNSIGNED attribute. For integer data types, ZEROFILL pads to the display width, if set. For other numeric data types, the display width is implicit in the ﬁeld deﬁnition. For example, a ﬁeld deﬁned as DOUBLE(8,4) has an implicit display width of three digits to the left of the decimal point and four digits to the right of the decimal point: mysql> USE test; Database changed mysql> CREATE TABLE double_zerofill ( -> db DOUBLE(8,4) NOT NULL DEFAULT 0, -> dz DOUBLE(8,4) ZEROFILL NOT NULL DEFAULT 0); Query OK, 0 rows affected (0.11 sec) mysql> SHOW CREATE TABLE double_zerofill\G *************************** 1. row *************************** Table: double_zerofill Create Table: CREATE TABLE `double_zerofill` ( `db` double(8,4) NOT NULL DEFAULT ’0.0000’, `dz` double(8,4) unsigned zerofill NOT NULL DEFAULT ’000.0000’ ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> INSERT INTO double_zerofill (db,dz) VALUES (1,1),(10,10), (100,100),(1000,1000); Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select db,dz FROM double_zerofill; +-----------+-----------+ | db | dz | +-----------+-----------+ | 1.0000 | 001.0000 | | 10.0000 | 010.0000 | | 100.0000 | 100.0000 | | 1000.0000 | 1000.0000 | +-----------+-----------+ 4 rows in set (0.01 sec) ■ SERIAL DEFAULT VALUE — The integer numeric types (TINYINT, SMALLINT, MEDIUM- INT, INT, BIGINT) allow the SERIAL DEFAULT VALUE keyword. This is shorthand for NOT NULL AUTO_INCREMENT UNIQUE KEY. Note that this is different from the SERIAL data type: mysql> CREATE TABLE serial_datatype (st1 SERIAL); 179
Part II Developing with MySQL Query OK, 0 rows affected (0.09 sec) mysql> SHOW CREATE TABLE serial_datatype\G *************************** 1. row *************************** Table: serial_datatype Create Table: CREATE TABLE `serial_datatype` ( `st1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, UNIQUE KEY `st1` (`st1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> CREATE TABLE serial_keyword (st2 INT SERIAL DEFAULT VALUE); Query OK, 0 rows affected (0.13 sec) mysql> SHOW CREATE TABLE serial_keyword\G *************************** 1. row *************************** Table: serial_keyword Create Table: CREATE TABLE `serial_keyword` ( `st2` int(11) NOT NULL AUTO_INCREMENT, UNIQUE KEY `st2` (`st2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Table 5-4 (on opposite page) shows a summary of the MySQL numeric data types. Boolean Types The ISO SQL:2003 standard deﬁnes a boolean data type of BOOLEAN. MySQL supports the stan- dard and adds a nonstandard abbreviation of BOOL. However, MySQL implements BOOLEAN as an alias for TINYINT(1): mysql> CREATE TABLE boolean_test ( -> bt1 BOOLEAN, bt2 BOOL, bt3 TINYINT(1)); Query OK, 0 rows affected (0.19 sec) mysql> SHOW CREATE TABLE boolean_test\G *************************** 1. row *************************** Table: boolean_test Create Table: CREATE TABLE `boolean_test` ( `bt1` tinyint(1) DEFAULT NULL, `bt2` tinyint(1) DEFAULT NULL, `bt3` tinyint(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec) mysql> INSERT INTO boolean_test (bt1, bt2, bt3) -> VALUES (true, 0, NULL); Query OK, 1 row affected (0.31 sec) 180
MySQL Data Types 5 TABLE 5-4 Summary of MySQL Numeric Data Types SQL Fixed/Variable Data Type Name Standard? Length Size Attributes NUMERIC Yes Different deﬁnitions See the ‘‘DECIMAL AUTO_INCREMENT will have different Size and Range’’ DEFAULT lengths; however, subsection in this NOT NULL these lengths are chapter NULL SIGNED ﬁxed once the ﬁeld is UNSIGNED deﬁned. ZEROFILL DECIMAL Yes Different deﬁnitions See the ‘‘DECIMAL DEFAULT will have different Size and Range’’ NOT NULL lengths; however, subsection in this NULL SIGNED these lengths are chapter UNSIGNED ﬁxed once the ﬁeld is ZEROFILL deﬁned. TINYINT No Fixed 1 byte AUTO_INCREMENT DEFAULT NOT NULL NULL SERIAL DEFAULT VALUE SIGNED UNSIGNED ZEROFILL SMALLINT Yes Fixed 2 bytes AUTO_INCREMENT DEFAULT NOT NULL NULL SERIAL DEFAULT VALUE SIGNED UNSIGNED ZEROFILL INT Yes Fixed 3 bytes AUTO_INCREMENT DEFAULT NOT NULL NULL SERIAL DEFAULT VALUE SIGNED UNSIGNED ZEROFILL continued 181
Part II Developing with MySQL TABLE 5-4 (continued ) SQL Fixed/Variable Data Type Name Standard? Length Size Attributes MEDIUMINT No Fixed 4 bytes AUTO_INCREMENT DEFAULT NOT NULL NULL SERIAL DEFAULT VALUE SIGNED UNSIGNED ZEROFILL BIGINT Yes Fixed 8 bytes AUTO_INCREMENT DEFAULT NOT NULL NULL SERIAL DEFAULT VALUE SIGNED UNSIGNED ZEROFILL FLOAT Yes Fixed 4 bytes AUTO_INCREMENT DEFAULT NOT NULL NULL SIGNED UNSIGNED ZEROFILL DOUBLE Yes Fixed 8 bytes AUTO_INCREMENT DEFAULT NOT NULL NULL SIGNED UNSIGNED ZEROFILL BIT No Variable See the ‘‘BIT Size and DEFAULT Range’’ subsection NOT NULL NULL REAL Yes REAL is an alias for DOUBLE or FLOAT, depending on sql_mode. SERIAL No SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE KEY. 182
MySQL Data Types 5 A value of 0 is false; non-zero values are true. mysql> INSERT INTO boolean_test (bt1, bt2, bt3) -> VALUES (true, 0, 5); Query OK, 1 row affected (0.20 sec) mysql> SELECT bt1, bt2, bt3 FROM boolean_test; +------+------+------+ | bt1 | bt2 | bt3 | +------+------+------+ | 1 | 0 | 5 | +------+------+------+ 1 row in set (0.00 sec) Recall that TINYINT(1) indicates a default width of 1, even though the ﬁeld itself will allow values ranging from –128 to 127. Datetime Types The ISO SQL:2003 standard deﬁnes the following three datetime types: ■ DATE ■ TIME(p) ■ TIMESTAMP(p) It also speciﬁes the following attributes: ■ WITH TIME ZONE ■ WITHOUT TIME ZONE MySQL supports these datetime types, although it does not support the two attributes nor the TIME and TIMESTAMP precision arguments. MySQL adds the following datetime data types: ■ YEAR ■ DATETIME The YEAR data type can be speciﬁed as YEAR(2) or YEAR(4). MySQL converts other values speciﬁed, including no value, as YEAR(4): mysql> create table year_test ( -> yt1 YEAR, yt2 YEAR(2), yt3 YEAR(1), -> yt4 YEAR(3), yt5 YEAR(7), yt6 YEAR(100)); Query OK, 0 rows affected (0.53 sec) mysql> SHOW CREATE TABLE year_test\G 183
Part II Developing with MySQL *************************** 1. row *************************** Table: year_test Create Table: CREATE TABLE `year_test` ( `yt1` year(4) DEFAULT NULL, `yt2` year(2) DEFAULT NULL, `yt3` year(4) DEFAULT NULL, `yt4` year(4) DEFAULT NULL, `yt5` year(4) DEFAULT NULL, `yt6` year(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The DATETIME data type contains the date in the format YYYY-mm-dd HH:ii:ss, the same as the TIMESTAMP format. However, DATETIME has a much larger range than TIMESTAMP does. See Table 5-5 for a summary of the datetime types, their ranges, sizes, and zero values. TABLE 5-5 Datetime Data Types Supported Range (Guaranteed to Work) Size Zero Value DATE ‘1000-01-01’ to ‘9999-12-31’ 3 bytes ‘0000-00-00’ DATETIME ‘1000-’01-01 00:00:01’ to ‘9999-12-31 23:59:59’ 8 bytes ‘0000-00-00 00:00:00’ TIMESTAMP ‘1970-01-01 00:00:00’ to ‘2038-01-18 22:14:07’ 4 bytes ‘0000-00-00 00:00:00’ TIME ‘–838:59:59’ to ‘838:59:58’ 3 bytes ‘00:00:00’ YEAR(2) 00 to 99 1 byte ‘00’’ YEAR(4) 1901 to 2155 1 byte ‘0000’ Note that DATE and TIME each have 3 bytes, and together they can represent the same values using 6 bytes a DATETIME value can using 8 bytes. If mysqld is running with an sql_mode of MAXDB, then TIMESTAMP is an alias for DATETIME, and the range and size are the same. The YEAR(2) type allows any two-digit unsigned integer. When using this type the year represented depends on the value. For values between 00 and 69, the year represented is 2000 through 2069. For values between 70 and 99, the year represented is 1970 through 1999. In this way, all of the years from 1970 through 2069 can be represented in 2 bytes. Unlike the other DATETIME data types, TIME can specify a time of day or an interval of time. 184
MySQL Data Types 5 Allowed input values The DATETIME, TIMESTAMP, and DATE values can be inputted in several different ways. The year part always comes ﬁrst, thus getting rid of the need to worry about whether a locale speciﬁes the date as month-day-year or day-month-year. Note that this is for data input only; the display of data can be formatted in many different ways, including showing the day or month ﬁrst. See Appendix B for how to use the DATE_FORMAT() function. The following formats are accepted, assuming they represent valid dates (for example, 99 is not a valid month): ■ YYYY-mm-dd HH:ii:ss ■ yy-mm-dd HH:ii:ss ■ YYYYmmdd ■ yymmdd — As a string or as a number, that is, ’20090508’ or 20090508 ■ YYYYmmddHHiiss — As a string or as a number ■ yymmddHHiiss — As a string or as a number ■ Actual datetime type values, such as the return from CURRENT_TIMESTAMP and CUR- RENT_DATE() If an input value contains an invalid DATETIME, TIMESTAMP, or DATE value, the zero value will be used instead. The many different types of allowed input can lead to some confusion. The briefest way to specify May 2009 is 090500, which corresponds to 2009-05-00. It is not possible for mysqld to interpret 0905 as a date, because there are not enough digits to represent a year, month, and day — this example could be May 2009 or September 5th. In practice, it is best to use one of the delimited formats for clariﬁcation. A string containing delimiters can use any punctuation in place of any other punctuation. For example, the date 2009-05-08 can be represented with any of the following: mysql> SELECT DATE(’2009-05-08’), DATE(’2009.05.08’), -> DATE(’2009!05@08’), DATE(’2009#05$08’), DATE(’2009%05 ˆ 08’), -> DATE(’2009&05*08’), DATE(’2009(05)08’), DATE(’2009`05∼08’), -> DATE(’2009;05"08’), DATE(’2009|05/08’), DATE(’2009?05>08’), -> DATE("2009’05
Part II Developing with MySQL DATE(’2009|05/08’): 2009-05-08 DATE(’2009?05>08’): 2009-05-08 DATE("2009’05