MySQL 8.0 Reference Manual（读书笔记40节-- Data Types(2)）
The string data types are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.
1.String Data Type Syntax
In some cases, MySQL may change a string column to a type different from that given in a CREATE TABLE or ALTER TABLE statement.
For definitions of character string columns (CHAR, VARCHAR, and the TEXT types), MySQL interprets【ɪnˈtɜːrprəts 诠释;说明;把…理解为;领会;口译;】 length specifications in character units. For definitions of binary string columns (BINARY, VARBINARY, and the BLOB types), MySQL interprets length specifications in byte units.
Column definitions for character string data types CHAR, VARCHAR, the TEXT types, ENUM, SET, and any synonyms) can specify the column character set and collation:
• CHARACTER SET specifies the character set. If desired, a collation for the character set can be specified with the COLLATE attribute, along with any other attributes. For example:
CREATE TABLE t ( c1 VARCHAR(20) CHARACTER SET utf8mb4, c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs );
CHARSET is a synonym for CHARACTER SET.
• Specifying the CHARACTER SET binary attribute for a character string data type causes the column to be created as the corresponding【ˌkɔːrəˈspɑːndɪŋ 相应的;相关的;符合的;】 binary string data type: CHAR becomes BINARY, VARCHAR becomes VARBINARY, and TEXT becomes BLOB. For the ENUM and SET data types, this does not occur; they are created as declared. Suppose that you specify a table using this definition:
CREATE TABLE t ( c1 VARCHAR(10) CHARACTER SET binary, c2 TEXT CHARACTER SET binary, c3 ENUM('a','b','c') CHARACTER SET binary );
The resulting table has this definition:
CREATE TABLE t ( c1 VARBINARY(10), c2 BLOB, c3 ENUM('a','b','c') CHARACTER SET binary );
• The BINARY attribute is a nonstandard MySQL extension that is shorthand for specifying the binary (_bin) collation of the column character set (or of the table default character set if no column character set is specified). In this case, comparison and sorting are based on numeric character code values. Suppose that you specify a table using this definition:
CREATE TABLE t ( c1 VARCHAR(10) CHARACTER SET latin1 BINARY, c2 TEXT BINARY ) CHARACTER SET utf8mb4;
The resulting table has this definition:
CREATE TABLE t ( c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin, c2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ) CHARACTER SET utf8mb4;
In MySQL 8.0, this nonstandard use of the BINARY attribute is ambiguous because the utf8mb4 character set has multiple _bin collations. As of MySQL 8.0.17, the BINARY attribute is deprecated and you should expect support for it to be removed in a future version of MySQL. Applications should be adjusted to use an explicit _bin collation instead.
The use of BINARY to specify a data type or character set remains unchanged.
• The ASCII attribute is shorthand for CHARACTER SET latin1. Supported in older MySQL releases, ASCII is deprecated in MySQL 8.0.28 and later; use CHARACTER SET instead.
• The UNICODE attribute is shorthand for CHARACTER SET ucs2. Supported in older MySQL releases, UNICODE is deprecated in MySQL 8.0.28 and later; use CHARACTER SET instead.
Character column comparison and sorting are based on the collation assigned to the column. For the CHAR, VARCHAR, TEXT, ENUM, and SET data types, you can declare a column with a binary (_bin) collation or the BINARY attribute to cause comparison and sorting to use the underlying character code values rather than a lexical【ˈleksɪkl 词汇的;】 ordering.
• [NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length in characters. The range of M is 0 to 255. If M is omitted, the length is 1.
补充：Trailing spaces are removed when CHAR values are retrieved unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.
CHAR is shorthand for CHARACTER. NATIONAL CHAR (or its equivalent short form, NCHAR) is the standard SQL way to define that a CHAR column should use some predefined character set. MySQL uses utf8mb3 as this predefined character set.
The CHAR BYTE data type is an alias for the BINARY data type. This is a compatibility feature.
MySQL permits you to create a column of type CHAR(0). This is useful primarily when you must be compliant【kəmˈplaɪənt 顺从的;(与系列规则相)符合的，一致的;百依百顺的;俯首帖耳的;】 with old applications that depend on the existence of a column but that do not actually use its value. CHAR(0) is also quite nice when you need a column that can take only two values: A column that is defined as CHAR(0) NULL occupies only one bit and can take only the values NULL and '' (the empty string).
• [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535. The effective【ɪˈfektɪv 有效的;生效的;实际的;事实上的;起作用的;产生预期结果的;】 maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8mb3 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8mb3 character set can be declared to be a maximum of 21,844 characters.
MySQL stores VARCHAR values as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A VARCHAR column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.
VARCHAR is shorthand for CHARACTER VARYING. NATIONAL VARCHAR is the standard SQL way to define that a VARCHAR column should use some predefined character set. MySQL uses utf8mb3 as this predefined character set. NVARCHAR is shorthand for NATIONAL VARCHAR.
• BINARY[(M)]
The BINARY type is similar to the CHAR type, but stores binary byte strings rather than nonbinary character strings. An optional length M represents the column length in bytes. If omitted, M defaults to 1.
• VARBINARY(M)
The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than nonbinary character strings. M represents the maximum column length in bytes.
• TINYBLOB
A BLOB column with a maximum length of 255 (28 − 1) bytes. Each TINYBLOB value is stored using a 1- byte length prefix that indicates the number of bytes in the value.
• TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 255 (28 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TINYTEXT value is stored using a 1-byte length prefix that indicates the number of bytes in the value.
• BLOB[(M)]
A BLOB column with a maximum length of 65,535 (216 − 1) bytes. Each BLOB value is stored using a 2- byte length prefix that indicates the number of bytes in the value.
An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest BLOB type large enough to hold values M bytes long.
• TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 65,535 (216 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value.
An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long.
• MEDIUMBLOB
A BLOB column with a maximum length of 16,777,215 (224 − 1) bytes. Each MEDIUMBLOB value is stored using a 3-byte length prefix that indicates the number of bytes in the value.
• MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 16,777,215 (224 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each MEDIUMTEXT value is stored using a 3- byte length prefix that indicates the number of bytes in the value.
• LONGBLOB
A BLOB column with a maximum length of 4,294,967,295 or 4GB (232 − 1) bytes. The effective maximum length of LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGBLOB value is stored using a 4-byte length prefix that indicates the number of bytes in the value.
• LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 − 1) characters. The effective maximum length is less if the value contains multibyte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value.
• ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special '' error value. ENUM values are represented internally as integers.
An ENUM column can have a maximum of 65,535 distinct elements
The maximum supported length of an individual ENUM element is M <= 255 and (M x w) <= 1020, where M is the element literal length and w is the number of bytes required for the maximum-length character in the character set.
• SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... SET values are represented internally as integers.
A SET column can have a maximum of 64 distinct members.
The maximum supported length of an individual SET element is M <= 255 and (M x w) <= 1020, where M is the element literal length and w is the number of bytes required for the maximum-length character in the character set.
2 The CHAR and VARCHAR Types
The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained【rɪˈteɪnd 保持;保留;持有;聘请(律师等);继续拥有;继续容纳;】.
The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.
The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.
If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds【ɪkˈsiːdz 超过(数量);超越(法律、命令等)的限制;】 the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode.
For VARCHAR columns, trailing spaces in excess【ɪkˈses , ˈekses 过度;过量的;超过;过分;免赔额;超过的量;自负额;过多的量;放肆行为;】 of the column length are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR columns, truncation of excess trailing spaces from inserted values is performed silently【ˈsaɪləntli 默默地;静静地;悄悄地;无声地;不用言语表达地;不说话地;】 regardless of the SQL mode.
VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.
The following table illustrates the differences between CHAR and VARCHAR by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns (assuming that the column uses a singlebyte character set such as latin1).
The values shown as stored in the last row of the table apply only when not using strict SQL mode; if strict mode is enabled, values that exceed the column length are not stored, and an error results.
InnoDB encodes fixed-length fields greater than or equal to 768 bytes in length as variable-length fields, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.
If a given value is stored into the CHAR(4) and VARCHAR(4) columns, the values retrieved from the columns are not always the same because trailing spaces are removed from CHAR columns upon retrieval. The following example illustrates this difference:
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO vc VALUES ('ab ', 'ab '); Query OK, 1 row affected (0.00 sec) mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc; +---------------------+---------------------+ | CONCAT('(', v, ')') | CONCAT('(', c, ')') | +---------------------+---------------------+ | (ab ) | (ab) | +---------------------+---------------------+ 1 row in set (0.06 sec)
Values in CHAR, VARCHAR, and TEXT columns are sorted and compared according to the character set collation assigned to the column.
MySQL collations have a pad attribute of PAD SPACE, other than Unicode collations based on UCA 9.0.0 and higher, which have a pad attribute of NO PAD.
To determine the pad attribute for a collation, use the INFORMATION_SCHEMA COLLATIONS table, which has a PAD_ATTRIBUTE column.
For nonbinary strings (CHAR, VARCHAR, and TEXT values), the string collation pad attribute determines treatment in comparisons of trailing spaces at the end of strings. NO PAD collations treat trailing spaces as significant in comparisons, like any other character. PAD SPACE collations treat trailing spaces as insignificant in comparisons; strings are compared without regard to trailing spaces.The server SQL mode has no effect on comparison behavior with respect to trailing spaces.
For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad characters results in a duplicate-key error. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error.
3 The BINARY and VARBINARY Types
The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they store binary strings rather than nonbinary strings. That is, they store byte strings rather than character strings. This means they have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in the values.
The permissible maximum length is the same for BINARY and VARBINARY as it is for CHAR and VARCHAR, except that the length for BINARY and VARBINARY is measured in bytes rather than characters.
The BINARY and VARBINARY data types are distinct from the CHAR BINARY and VARCHAR BINARY data types. For the latter types, the BINARY attribute does not cause the column to be treated as a binary string column. Instead, it causes the binary (_bin) collation for the column character set (or the table default character set if no column character set is specified) to be used, and the column itself stores nonbinary character strings rather than binary byte strings. For example, if the default character set is utf8mb4, CHAR(5) BINARY is treated as CHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin. This differs from BINARY(5), which stores 5-byte binary strings that have the binary character set and collation.
If strict SQL mode is not enabled and you assign a value to a BINARY or VARBINARY column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For cases of truncation, to cause an error to occur (rather than a warning) and suppress insertion of the value, use strict SQL mode.
When BINARY values are stored, they are right-padded with the pad value to the specified length. The pad value is 0x00 (the zero byte). Values are right-padded with 0x00 for inserts, and no trailing bytes are removed for retrievals. All bytes are significant in comparisons, including ORDER BY and DISTINCT operations. 0x00 and space differ in comparisons, with 0x00 sorting before space.
Example: For a BINARY(3) column, 'a ' becomes 'a \0' when inserted. 'a\0' becomes 'a\0\0' when inserted. Both inserted values remain unchanged for retrievals.
For VARBINARY, there is no padding for inserts and no bytes are stripped for retrievals. All bytes are significant in comparisons, including ORDER BY and DISTINCT operations. 0x00 and space differ in comparisons, with 0x00 sorting before space.
For those cases where trailing pad bytes are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting values into the column that differ only in number of trailing pad bytes results in a duplicate-key error. For example, if a table contains 'a', an attempt to store 'a\0' causes a duplicate-key error.
You should consider the preceding padding and stripping characteristics carefully if you plan to use the BINARY data type for storing binary data and you require that the value retrieved be exactly the same as the value stored. The following example illustrates how 0x00-padding of BINARY values affects column value comparisons:
mysql> CREATE TABLE t (c BINARY(3)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t SET c = 'a'; Query OK, 1 row affected (0.01 sec) mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t; +--------+---------+-------------+ | HEX(c) | c = 'a' | c = 'a\0\0' | +--------+---------+-------------+ | 610000 | 0 | 1 | +--------+---------+-------------+ 1 row in set (0.09 sec)
If the value retrieved must be the same as the value specified for storage with no padding, it might be preferable to use VARBINARY or one of the BLOB data types instead.
4 The BLOB and TEXT Types
A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements.
BLOB values are treated as binary strings (byte strings). They have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). They have a character set other than binary, and values are sorted and compared based on the collation of the character set.
If strict SQL mode is not enabled and you assign a value to a BLOB or TEXT column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode.
Truncation of excess trailing spaces from values to be inserted into TEXT columns always generates a warning, regardless of the SQL mode.
For TEXT and BLOB columns, there is no padding on insert and no bytes are stripped on select
If a TEXT column is indexed, index entry comparisons are space-padded at the end. This means that, if the index requires unique values, duplicate-key errors occur for values that differ only in the number of trailing spaces. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error. This is not true for BLOB columns.
In most respects, you can regard a BLOB column as a VARBINARY column that can be as large as you like. Similarly, you can regard a TEXT column as a VARCHAR column. BLOB and TEXT differ from VARBINARY and VARCHAR in the following ways:
• For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional.
• BLOB and TEXT columns cannot have DEFAULT values.
If you use the BINARY attribute with a TEXT data type, the column is assigned the binary (_bin) collation of the column character set.
LONG and LONG VARCHAR map to the MEDIUMTEXT data type. This is a compatibility【kəmˌpætəˈbɪləti (尤指计算机及程序的)兼容性，相容性;相容;并存;和睦相处;】 feature.
MySQL Connector/ODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR.
Because BLOB and TEXT values can be extremely long, you might encounter some constraints in using them:
• Only the first max_sort_length bytes of the column are used when sorting. The default value of max_sort_length is 1024. You can make more bytes significant in sorting or grouping by increasing the value of max_sort_length at server startup or runtime. Any client can change the value of its session max_sort_length variable:
mysql> SET max_sort_length = 2000; mysql> SELECT id, comment FROM t -> ORDER BY comment;
• Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the MEMORY storage engine does not support those data types. Use of disk incurs【ɪnˈkɜːrz 招致;引起;遭受;引致，带来(成本、花费等);】 a performance penalty【ˈpenəlti 处罚;惩罚;刑罚;点球;(对犯规者的)判罚;不利;害处;】, so include BLOB or TEXT columns in the query result only if they are really needed. For example, avoid using SELECT *, which selects all columns.
• The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you actually can transmit【trænzˈmɪt 传输;使通过;传送;发射;传播;】 between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the max_allowed_packet variable, but you must do so for both the server and your client program. For example, both mysql and mysqldump enable you to change the client-side max_allowed_packet value.
Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened.
In some cases, it may be desirable to store binary data such as media files in BLOB or TEXT columns. You may find MySQL's string handling functions useful for working with such data.For security and other reasons, it is usually preferable to do so using application code rather than giving application users the FILE privilege. You can discuss specifics for various languages and platforms in the MySQL Forums (http://forums.mysql.com/).
5 The ENUM Type
An ENUM is a string object with a value chosen from a list of permitted values that are enumerated【ɪˈnuːməreɪtɪd 枚举;列举;】 explicitly in the column specification at table creation time.
The ENUM type has these advantages:
• Compact data storage in situations where a column has a limited set of possible values. The strings you specify as input values are automatically encoded as numbers.
• Readable queries and output. The numbers are translated back to the corresponding strings in query results.
and these potential issues to consider:
• If you make enumeration values that look like numbers, it is easy to mix up the literal values with their internal index numbers.
• Using ENUM columns in ORDER BY clauses requires extra care.
5.1 Creating and Using ENUM Columns
An enumeration value must be a quoted【ˈkwoʊtɪd 报价;引用;引述;出价;开价;举例说明;】 string literal. For example, you can create a table with an ENUM column like this:
CREATE TABLE shirts ( name VARCHAR(40), size ENUM('x-small', 'small', 'medium', 'large', 'x-large') ); INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'), ('polo shirt','small'); SELECT name, size FROM shirts WHERE size = 'medium'; +---------+--------+ | name | size | +---------+--------+ | t-shirt | medium | +---------+--------+ UPDATE shirts SET size = 'small' WHERE size = 'large'; COMMIT;
Inserting 1 million rows into this table with a value of 'medium' would require 1 million bytes of storage, as opposed to 6 million bytes if you stored the actual string 'medium' in a VARCHAR column.
5.2 Index Values for Enumeration Literals
Each enumeration value has an index:
• The elements listed in the column specification are assigned index numbers, beginning with 1.
• The index value of the empty string error value is 0. This means that you can use the following SELECT statement to find rows into which invalid ENUM values were assigned:
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
• The index of the NULL value is NULL.
• The term “index” here refers to a position within the list of enumeration values. It has nothing to do with table indexes.
For example, a column specified as ENUM('Mercury', 'Venus', 'Earth') can have any of the values shown here. The index of each value is also shown.
An ENUM column can have a maximum of 65,535 distinct elements.
If you retrieve an ENUM value in a numeric context, the column value's index is returned. For example, you can retrieve numeric values from an ENUM column like this:
mysql> SELECT enum_col+0 FROM tbl_name;
Functions such as SUM() or AVG() that expect a numeric argument cast the argument to a number if necessary. For ENUM values, the index number is used in the calculation.
5.3 Handling of Enumeration Literals
Trailing spaces are automatically deleted from ENUM member values in the table definition when a table is created.
When retrieved, values stored into an ENUM column are displayed using the lettercase that was used in the column definition. Note that ENUM columns can be assigned a character set and collation. For binary or case-sensitive collations, lettercase is taken into account when assigning values to the column.
If you store a number into an ENUM column, the number is treated as the index into the possible values, and the value stored is the enumeration member with that index. (However, this does not work with LOAD DATA, which treats all input as strings.) If the numeric value is quoted, it is still interpreted as an index if there is no matching string in the list of enumeration values. For these reasons, it is not advisable to define an ENUM column with enumeration values that look like numbers, because this can easily become confusing. For example, the following column has enumeration members with string values of '0', '1', and '2', but numeric index values of 1, 2, and 3:
numbers ENUM('0','1','2')
If you store 2, it is interpreted as an index value, and becomes '1' (the value with index 2). If you store '2', it matches an enumeration value, so it is stored as '2'. If you store '3', it does not match any enumeration value, so it is treated as an index and becomes '2' (the value with index 3).
mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3'); mysql> SELECT * FROM t; +---------+ | numbers | +---------+ | 1 | | 2 | | 2 | +---------+
To determine all possible values for an ENUM column, use SHOW COLUMNS FROM tbl_name LIKE 'enum_col' and parse the ENUM definition in the Type column of the output.
In the C API, ENUM values are returned as strings.
5.4 Empty or NULL Enumeration Values
An enumeration value can also be the empty string ('') or NULL under certain circumstances:
• If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value. This string can be distinguished from a “normal” empty string by the fact that this string has the numeric value 0.
If strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error.
• If an ENUM column is declared to permit NULL, the NULL value is a valid value for the column, and the default value is NULL. If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values.
5.5 Enumeration Sorting
ENUM values are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example, 'b' sorts before 'a' for ENUM('b', 'a'). The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values.
To prevent unexpected results when using the ORDER BY clause on an ENUM column, use one of these techniques:
• Specify the ENUM list in alphabetic order.
• Make sure that the column is sorted lexically rather than by index number by coding ORDER BY CAST(col AS CHAR) or ORDER BY CONCAT(col).
5.6 Enumeration Limitations
An enumeration value cannot be an expression, even one that evaluates to a string value.
For example, this CREATE TABLE statement does not work because the CONCAT function cannot be used to construct an enumeration value:
CREATE TABLE sizes ( size ENUM('small', CONCAT('med','ium'), 'large') );
You also cannot employ a user variable as an enumeration value. This pair of statements do not work:
SET @mysize = 'medium'; CREATE TABLE sizes ( size ENUM('small', @mysize, 'large') );
We strongly recommend that you do not use numbers as enumeration values, because it does not save on storage over the appropriate TINYINT or SMALLINT type, and it is easy to mix up the strings and the underlying number values (which might not be the same) if you quote the ENUM values incorrectly. If you do use a number as an enumeration value, always enclose it in quotation marks. If the quotation marks are omitted, the number is regarded as an index.
Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.
6 The SET Type
A SET is a string object that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created. SET column values that consist of multiple set members are specified with members separated by commas (,). A consequence of this is that SET member values should not themselves contain commas.
For example, a column specified as SET('one', 'two') NOT NULL can have any of these values:
'' 'one' 'two' 'one,two'
A SET column can have a maximum of 64 distinct members.
Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.
Trailing spaces are automatically deleted from SET member values in the table definition when a table is created.
When retrieved, values stored in a SET column are displayed using the lettercase that was used in the column definition. Note that SET columns can be assigned a character set and collation. For binary or case-sensitive collations, lettercase is taken into account when assigning values to the column.
MySQL stores SET values numerically, with the low-order bit of the stored value corresponding to the first set member. If you retrieve a SET value in a numeric context, the value retrieved has bits set corresponding to the set members that make up the column value. For example, you can retrieve numeric values from a SET column like this:
mysql> SELECT set_col+0 FROM tbl_name;
If a number is stored into a SET column, the bits that are set in the binary representation of the number determine the set members in the column value. For a column specified as SET('a','b','c','d'), the members have the following decimal and binary values.
If you assign a value of 9 to this column, that is 1001 in binary, so the first and fourth SET value members 'a' and 'd' are selected and the resulting value is 'a,d'.
For a value containing more than one SET element, it does not matter what order the elements are listed in when you insert the value. It also does not matter how many times a given element is listed in the value. When the value is retrieved later, each element in the value appears once, with elements listed according to the order in which they were specified at table creation time. Suppose that a column is specified as SET('a','b','c','d'):
mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
If you insert the values 'a,d', 'd,a', 'a,d,d', 'a,d,a', and 'd,a,d':
mysql> INSERT INTO myset (col) VALUES -> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d'); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0
Then all these values appear as 'a,d' when retrieved:
mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.04 sec)
If you set a SET column to an unsupported value, the value is ignored and a warning is issued:
mysql> INSERT INTO myset (col) VALUES ('a,d,d,s'); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> SHOW WARNINGS; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1265 | Data truncated for column 'col' at row 1 | +---------+------+------------------------------------------+ 1 row in set (0.04 sec) mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 6 rows in set (0.01 sec)
If strict SQL mode is enabled, attempts to insert invalid SET values result in an error.
SET values are sorted numerically. NULL values sort before non-NULL SET values.
Functions such as SUM() or AVG() that expect a numeric argument cast the argument to a number if necessary. For SET values, the cast operation causes the numeric value to be used.
Normally, you search for SET values using the FIND_IN_SET() function or the LIKE operator:
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0; mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
The first statement finds rows where set_col contains the value set member. The second is similar, but not the same: It finds rows where set_col contains value anywhere, even as a substring of another set member.
The following statements also are permitted:
mysql> SELECT * FROM tbl_name WHERE set_col & 1; mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
The first of these statements looks for values containing the first set member. The second looks for an exact match. Be careful with comparisons of the second type. Comparing set values to 'val1,val2' returns different results than comparing values to 'val2,val1'. You should specify the values in the same order they are listed in the column definition.
To determine all possible values for a SET column, use SHOW COLUMNS FROM tbl_name LIKE set_col and parse【pɑːrs 对作语法分析】 the SET definition in the Type column of the output.
In the C API, SET values are returned as strings.