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.
• CHAR[(M)]
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).
• VARCHAR(M)
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
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)]
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
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
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',...)
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 MCREATE 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> 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)• 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:
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)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> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM t
-> ORDER BY comment;• 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:
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;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:
mysql> SELECT * FROM tbl_name WHERE enum_col=0;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> SELECT enum_col+0 FROM tbl_name;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:
numbers ENUM('0','1','2')You also cannot employ a user variable as an enumeration value. This pair of statements do not work:
mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql> SELECT * FROM t;
+---------+
| numbers |
+---------+
| 1 |
| 2 |
| 2 |
+---------+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:
CREATE TABLE sizes (
size ENUM('small', CONCAT('med','ium'), 'large')
);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:
SET @mysize = 'medium';
CREATE TABLE sizes (
size ENUM('small', @mysize, 'large')
);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'):
''
'one'
'two'
'one,two'If you insert the values 'a,d', 'd,a', 'a,d,d', 'a,d,a', and 'd,a,d':
mysql> SELECT set_col+0 FROM tbl_name;Then all these values appear as 'a,d' when retrieved:
mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));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,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0If 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 col FROM myset;
+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
5 rows in set (0.04 sec)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> 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)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.
来源:https://www.cnblogs.com/xuliuzai/p/18139505
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]