Menu:
set:: The SET Type
The string data types are note 'CHAR': char, note 'VARCHAR': char, note 'BINARY': binary-varbinary, note 'VARBINARY': binary-varbinary, note 'BLOB': blob, note 'TEXT': blob, note 'ENUM': enum, and note 'SET': set.
For information about storage requirements of the string data types, see *note storage-requirements::.
For descriptions of functions that operate on string values, see *note string-functions::.
File: manual.info.tmp, Node: string-type-syntax, Next: char, Prev: string-types, Up: string-types
The string data types are note 'CHAR': char, note 'VARCHAR': char, note 'BINARY': binary-varbinary, note 'VARBINARY': binary-varbinary, note 'BLOB': blob, note 'TEXT': blob, note 'ENUM': enum, and note 'SET': set.
In some cases, MySQL may change a string column to a type different from that given in a note 'CREATE TABLE': create-table. or note 'ALTER TABLE': alter-table. statement. See *note silent-column-changes::.
For definitions of character string columns (note 'CHAR': char, note 'VARCHAR': char, and the note 'TEXT': blob. types), MySQL interprets length specifications in character units. For definitions of binary string columns (note 'BINARY': binary-varbinary, note 'VARBINARY': binary-varbinary, and the note 'BLOB': blob. types), MySQL interprets length specifications in byte units.
Column definitions for character string data types note 'CHAR': char, note 'VARCHAR': char, the note 'TEXT': blob. types, note 'ENUM': enum, *note 'SET': 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 utf8,
c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);This table definition creates a column named 'c1' that has a character set of 'utf8' with the default collation for that character set, and a column named 'c2' that has a character set of 'latin1' and a case-sensitive ('_cs') collation.
The rules for assigning the character set and collation when either or both of 'CHARACTER SET' and the 'COLLATE' attribute are missing are described in *note charset-column::.
'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 binary string data type: note 'CHAR': char. becomes note 'BINARY': binary-varbinary, note 'VARCHAR': char. becomes note 'VARBINARY': binary-varbinary, and note 'TEXT': blob. becomes note 'BLOB': blob. For the note 'ENUM': enum. and note 'SET': 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;The 'ASCII' attribute is shorthand for 'CHARACTER SET latin1'.
The 'UNICODE' attribute is shorthand for 'CHARACTER SET ucs2'.
Character column comparison and sorting are based on the collation assigned to the column. For the note 'CHAR': char, note 'VARCHAR': char, note 'TEXT': blob, note 'ENUM': enum, and *note 'SET': 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 ordering.
For additional information about use of character sets in MySQL, see *note charset::.
'[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.
Note:
Trailing spaces are removed when *note 'CHAR': char. values are retrieved unless the 'PAD_CHAR_TO_FULL_LENGTH' SQL mode is enabled.
note 'CHAR': char. is shorthand for note 'CHARACTER': char. note 'NATIONAL CHAR': char. (or its equivalent short form, note 'NCHAR': char.) is the standard SQL way to define that a note 'CHAR': char. column should use some predefined character set. MySQL uses 'utf8' as this predefined character set. note charset-national::.
The note 'CHAR BYTE': binary-varbinary. data type is an alias for the note 'BINARY': binary-varbinary. 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 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 maximum length of a note 'VARCHAR': char. is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, 'utf8' characters can require up to three bytes per character, so a note 'VARCHAR': char. column that uses the 'utf8' character set can be declared to be a maximum of 21,844 characters. See *note column-count-limit::.
MySQL stores note 'VARCHAR': char. values as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A note 'VARCHAR': char. column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.
Note:
MySQL follows the standard SQL specification, and does not remove trailing spaces from *note 'VARCHAR': char. values.
note 'VARCHAR': char. is shorthand for note 'CHARACTER VARYING': char. note 'NATIONAL VARCHAR': char. is the standard SQL way to define that a note 'VARCHAR': char. column should use some predefined character set. MySQL uses 'utf8' as this predefined character set. note charset-national::. note 'NVARCHAR': char. is shorthand for *note 'NATIONAL VARCHAR': char.
*note 'BINARY[(M)]': binary-varbinary.
The note 'BINARY': binary-varbinary. type is similar to the note 'CHAR': 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.
*note 'VARBINARY(M)': binary-varbinary.
The note 'VARBINARY': binary-varbinary. type is similar to the note 'VARCHAR': char. type, but stores binary byte strings rather than nonbinary character strings. M represents the maximum column length in bytes.
*note 'TINYBLOB': blob.
A *note 'BLOB': blob. column with a maximum length of 255 (2^8 −*note 'TINYTEXT [CHARACTER SET CHARSET_NAME] [COLLATE COLLATION_NAME]': blob.
A *note 'TEXT': blob. column with a maximum length of 255 (2^8 −*note 'BLOB[(M)]': blob.
A note 'BLOB': blob. column with a maximum length of 65,535 (2^16 − 1) bytes. Each note 'BLOB': 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 *note 'BLOB': blob. type large enough to hold values M bytes long.
*note 'TEXT[(M)] [CHARACTER SET CHARSET_NAME] [COLLATE COLLATION_NAME]': blob.
A note 'TEXT': blob. column with a maximum length of 65,535 (2^16 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each note 'TEXT': 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 *note 'TEXT': blob. type large enough to hold values M characters long.
*note 'MEDIUMBLOB': blob.
A note 'BLOB': blob. column with a maximum length of 16,777,215 (2^24 − 1) bytes. Each note 'MEDIUMBLOB': blob. value is stored using a 3-byte length prefix that indicates the number of bytes in the value.
*note 'MEDIUMTEXT [CHARACTER SET CHARSET_NAME] [COLLATE COLLATION_NAME]': blob.
A note 'TEXT': blob. column with a maximum length of 16,777,215 (2^24 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each note 'MEDIUMTEXT': blob. value is stored using a 3-byte length prefix that indicates the number of bytes in the value.
*note 'LONGBLOB': blob.
A note 'BLOB': blob. column with a maximum length of 4,294,967,295 or 4GB (2^32 − 1) bytes. The effective maximum length of note 'LONGBLOB': blob. columns depends on the configured maximum packet size in the client/server protocol and available memory. Each *note 'LONGBLOB': blob. value is stored using a 4-byte length prefix that indicates the number of bytes in the value.
*note 'LONGTEXT [CHARACTER SET CHARSET_NAME] [COLLATE COLLATION_NAME]': blob.
A note 'TEXT': blob. column with a maximum length of 4,294,967,295 or 4GB (2^32 − 1) characters. The effective maximum length is less if the value contains multibyte characters. The effective maximum length of note 'LONGTEXT': blob. columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each *note 'LONGTEXT': blob. value is stored using a 4-byte length prefix that indicates the number of bytes in the value.
*note 'ENUM('VALUE1','VALUE2',...) [CHARACTER SET CHARSET_NAME][COLLATE COLLATION_NAME]': enum.
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. *note 'ENUM': enum. values are represented internally as integers.
An note 'ENUM': enum. column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.) A table can have no more than 255 unique element list definitions among its note 'ENUM': enum. and note 'SET': set. columns considered as a group. For more information on these limits, see note limits-frm-file::.
*note 'SET('VALUE1','VALUE2',...) [CHARACTER SET CHARSET_NAME][COLLATE COLLATION_NAME]': set.
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'', '...' *note 'SET': set. values are represented internally as integers.
A note 'SET': set. column can have a maximum of 64 distinct members. A table can have no more than 255 unique element list definitions among its note 'ENUM': enum. and note 'SET': set. columns considered as a group. For more information on this limit, see note limits-frm-file::.
File: manual.info.tmp, Node: char, Next: binary-varbinary, Prev: string-type-syntax, Up: string-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.
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. See *note column-count-limit::.
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 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. See *note sql-mode::.
For 'VARCHAR' columns, trailing spaces in excess 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 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 single-byte character set such as 'latin1').
Value 'CHAR(4)' Storage 'VARCHAR(4)'Storage Required Required
''''
'' '' 4 bytes '''' 1 byte
''ab''
''ab '' 4 bytes ''ab'' 3 bytes
''abcd''
''abcd'' 4 bytes ''abcd'' 5 bytes
''abcdefgh''
''abcd'' 4 bytes ''abcd'' 5 bytes
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.
All MySQL collations are of type 'PAD SPACE'. This means that all 'CHAR', 'VARCHAR', and 'TEXT' values are compared without regard to any trailing spaces. 'Comparison' in this context does not include the 'LIKE' pattern-matching operator, for which trailing spaces are significant. For example:
mysql> CREATE TABLE names (myname CHAR(10));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO names VALUES ('Jones');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT myname = 'Jones', myname = 'Jones ' FROM names;
+------------------+--------------------+
| myname = 'Jones' | myname = 'Jones ' |
+------------------+--------------------+
| 1 | 1 |
+------------------+--------------------+
1 row in set (0.00 sec)
mysql> SELECT myname LIKE 'Jones', myname LIKE 'Jones ' FROM names;
+---------------------+-----------------------+
| myname LIKE 'Jones' | myname LIKE 'Jones ' |
+---------------------+-----------------------+
| 1 | 0 |
+---------------------+-----------------------+
1 row in set (0.00 sec)This is not affected by the server SQL mode.
Note:
For more information about MySQL character sets and collations, see note charset::. For additional information about storage requirements, see note storage-requirements::.
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.
File: manual.info.tmp, Node: binary-varbinary, Next: blob, Prev: char, Up: string-types
The 'BINARY' and 'VARBINARY' types are similar to note 'CHAR': char. and note 'VARCHAR': char, 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 note 'CHAR': char. and note 'VARCHAR': char, 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 'latin1', 'CHAR(5) BINARY' is treated as 'CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin'. This differs from 'BINARY(5)', which stores 5-byte binary strings that have the 'binary' character set and collation. For information about the differences between the 'binary' collation of the 'binary' character set and the '_bin' collations of nonbinary character sets, see *note charset-binary-collations::.
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. See *note 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 '' when inserted. ''a'' becomes ''a'' 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'' 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 *note 'BLOB': blob. data types instead.
Note:
Within the note 'mysql': mysql. client, binary strings display using hexadecimal notation, depending on the value of the '--binary-as-hex'. For more information about that option, see note mysql::.
File: manual.info.tmp, Node: blob, Next: enum, Prev: binary-varbinary, Up: string-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. See *note 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. See *note sql-mode::.
Truncation of excess trailing spaces from values to be inserted into *note 'TEXT': blob. 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 note 'VARBINARY': binary-varbinary. column that can be as large as you like. Similarly, you can regard a 'TEXT' column as a note 'VARCHAR': char. column. 'BLOB' and 'TEXT' differ from note 'VARBINARY': binary-varbinary. and note 'VARCHAR': char. in the following ways:
For indexes on 'BLOB' and 'TEXT' columns, you must specify an index prefix length. For note 'CHAR': char. and note 'VARCHAR': char, a prefix length is optional. See *note column-indexes::.
'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 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 (see note internal-temporary-tables::). Use of disk incurs a performance penalty, so include 'BLOB' or 'TEXT' columns in the query result only if they are really needed. For example, avoid using note 'SELECT *': 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 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 note 'mysql': mysql. and note 'mysqldump': mysqldump. enable you to change the client-side 'max_allowed_packet' value. See note server-configuration::, note mysql::, and note mysqldump::. You may also want to compare the packet sizes and the size of the data objects you are storing with the storage requirements, see note storage-requirements::
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. See *note string-functions::. 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/).
Note:
Within the note 'mysql': mysql. client, binary strings display using hexadecimal notation, depending on the value of the '--binary-as-hex'. For more information about that option, see note mysql::.
File: manual.info.tmp, Node: enum, Next: set, Prev: blob, Up: string-types
An 'ENUM' is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.
See note string-type-syntax:: for note 'ENUM': enum. type syntax and length limits.
The *note 'ENUM': 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. See *note storage-requirements:: for storage requirements for the 'ENUM' type.
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, as explained in *note enum-limits::.
Using 'ENUM' columns in 'ORDER BY' clauses requires extra care, as explained in *note enum-sorting::.
*note enum-using::
*note enum-indexes::
*note enum-literals::
*note enum-nulls::
*note enum-sorting::
*note enum-limits::
Creating and Using ENUM Columns
An enumeration value must be a quoted 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.
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 *note 'SELECT': 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.
Value Index
'NULL' 'NULL'
'''' 0
''Mercury'' 1
''Venus'' 2
''Earth'' 3
An note 'ENUM': enum. column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.) A table can have no more than 255 unique element list definitions among its note 'ENUM': enum. and note 'SET': set. columns considered as a group. For more information on these limits, see note limits-frm-file::.
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.
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 *note 'LOAD DATA': 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 *note 'SHOW COLUMNS FROM TBL_NAME LIKE 'ENUM_COL'': show-columns. and parse the 'ENUM' definition in the 'Type' column of the output.
In the C API, 'ENUM' values are returned as strings. For information about using result set metadata to distinguish them from other strings, see C API Basic Data Structures (https://dev.mysql.com/doc/c-api/5.7/en/c-api-data-structures.html).
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. See *note enum-indexes:: for details about the numeric indexes for the enumeration values.
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.
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)'.
Enumeration Limitations
An enumeration value cannot be an expression, even one that evaluates to a string value.
For example, this *note 'CREATE TABLE': 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 note 'TINYINT': integer-types. or note 'SMALLINT': integer-types. 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. See *note enum-literals:: to see how even a quoted number could be mistakenly used as a numeric index value.
Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.
File: manual.info.tmp, Node: set, Prev: enum, Up: string-types
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 note 'SET': set. column can have a maximum of 64 distinct members. A table can have no more than 255 unique element list definitions among its note 'ENUM': enum. and note 'SET': set. columns considered as a group. For more information on this limit, see note limits-frm-file::.
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.
See note data-types-storage-reqs-strings:: for storage requirements for the note 'SET': set. type.
See note string-type-syntax:: for note 'SET': set. type syntax and length limits.
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.
'SET' Decimal Binary Value Member Value
''a''
'1' '0001'
''b''
'2' '0010'
''c''
'4' '0100'
''d''
'8' '1000'
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: 0Then 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 the 'SET' definition in the 'Type' column of the output.
In the C API, 'SET' values are returned as strings. For information about using result set metadata to distinguish them from other strings, see C API Basic Data Structures (https://dev.mysql.com/doc/c-api/5.7/en/c-api-data-structures.html).
File: manual.info.tmp, Node: spatial-types, Next: json, Prev: string-types, Up: data-types