Movatterモバイル変換

CHARACTER SET specifies the character set. If desired, a collation for the character set can be specified with theCOLLATE 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);

This table definition creates a column namedc1 that has a character set ofutf8mb4 with the default collation for that character set, and a column namedc2 that has a character set oflatin1 and a case-sensitive (_cs) collation.

The rules for assigning the character set and collation when either or both ofCHARACTER SET and theCOLLATE attribute are missing are described inSection 12.3.5, “Column Character Set and Collation”.

CHARSET is a synonym forCHARACTER SET.

Specifying theCHARACTER SET binary attribute for a character string data type causes the column to be created as the corresponding binary string data type:CHAR becomesBINARY,VARCHAR becomesVARBINARY, andTEXT becomesBLOB. For theENUM andSET 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);

TheBINARY 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 9.4, theBINARY 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 ofBINARY to specify a data type or character set remains unchanged.

TheASCII attribute is shorthand forCHARACTER SET latin1. Supported in older MySQL releases,ASCII is deprecated; useCHARACTER SET instead.

TheUNICODE attribute is shorthand forCHARACTER SET ucs2. Supported in older MySQL releases,UNICODE is deprecated; useCHARACTER SET instead.

[NATIONAL] CHAR[(M)] [CHARACTER SETcharset_name] [COLLATEcollation_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 ofM is 0 to 255. IfM is omitted, the length is 1.

CHAR is shorthand forCHARACTER.NATIONAL CHAR (or its equivalent short form,NCHAR) is the standard SQL way to define that aCHAR column should use some predefined character set. MySQL usesutf8mb3 as this predefined character set.Section 12.3.7, “The National Character Set”.

TheCHAR BYTE data type is an alias for theBINARY data type. This is a compatibility feature.

MySQL permits you to create a column of typeCHAR(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 asCHAR(0) NULL occupies only one bit and can take only the valuesNULL and'' (the empty string).

[NATIONAL] VARCHAR(M) [CHARACTER SETcharset_name] [COLLATEcollation_name]

A variable-length string.M represents the maximum column length in characters. The range ofM is 0 to 65,535. The effective maximum length of aVARCHAR 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 aVARCHAR column that uses theutf8mb3 character set can be declared to be a maximum of 21,844 characters. SeeSection 10.4.7, “Limits on Table Column Count and Row Size”.

MySQL storesVARCHAR values as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. AVARCHAR 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 forCHARACTER VARYING.NATIONAL VARCHAR is the standard SQL way to define that aVARCHAR column should use some predefined character set. MySQL usesutf8mb3 as this predefined character set.Section 12.3.7, “The National Character Set”.NVARCHAR is shorthand forNATIONAL VARCHAR.

BINARY[(M)]

TheBINARY type is similar to theCHAR type, but stores binary byte strings rather than nonbinary character strings. An optional lengthM represents the column length in bytes. If omitted,M defaults to 1.

VARBINARY(M)

TheVARBINARY type is similar to theVARCHAR type, but stores binary byte strings rather than nonbinary character strings.M represents the maximum column length in bytes.

TINYBLOB

ABLOB column with a maximum length of 255 (2⁸ − 1) bytes. EachTINYBLOB value is stored using a 1-byte length prefix that indicates the number of bytes in the value.

TINYTEXT [CHARACTER SETcharset_name] [COLLATEcollation_name]

ATEXT column with a maximum length of 255 (2⁸ − 1) characters. The effective maximum length is less if the value contains multibyte characters. EachTINYTEXT value is stored using a 1-byte length prefix that indicates the number of bytes in the value.

BLOB[(M)]

ABLOB column with a maximum length of 65,535 (2¹⁶ − 1) bytes. EachBLOB value is stored using a 2-byte length prefix that indicates the number of bytes in the value.

An optional lengthM can be given for this type. If this is done, MySQL creates the column as the smallestBLOB type large enough to hold valuesM bytes long.

TEXT[(M)] [CHARACTER SETcharset_name] [COLLATEcollation_name]

ATEXT column with a maximum length of 65,535 (2¹⁶ − 1) bytes. The effective maximum length is less if the value contains multibyte characters. EachTEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value.

An optional lengthM can be given for this type. If this is done, MySQL creates the column as the smallestTEXT type large enough to hold valuesM characters long.

MEDIUMBLOB

ABLOB column with a maximum length of 16,777,215 (2²⁴ − 1) bytes. EachMEDIUMBLOB value is stored using a 3-byte length prefix that indicates the number of bytes in the value.

MEDIUMTEXT [CHARACTER SETcharset_name] [COLLATEcollation_name]

ATEXT column with a maximum length of 16,777,215 (2²⁴ − 1) characters. The effective maximum length is less if the value contains multibyte characters. EachMEDIUMTEXT value is stored using a 3-byte length prefix that indicates the number of bytes in the value.

LONGBLOB

ABLOB column with a maximum length of 4,294,967,295 or 4GB (2³² − 1) bytes. The effective maximum length ofLONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory. EachLONGBLOB value is stored using a 4-byte length prefix that indicates the number of bytes in the value.

LONGTEXT [CHARACTER SETcharset_name] [COLLATEcollation_name]

ATEXT column with a maximum length of 4,294,967,295 or 4GB (2³² − 1) characters. The effective maximum length is less if the value contains multibyte characters. The effective maximum length ofLONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. EachLONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value.

ENUM('value1','value2',...) [CHARACTER SETcharset_name] [COLLATEcollation_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.

AnENUM column can have a maximum of 65,535 distinct elements.

The maximum supported length of an individualENUM element isM <= 255 and (M xw) <= 1020, whereM is the element literal length andw is the number of bytes required for the maximum-length character in the character set.

SET('value1','value2',...) [CHARACTER SETcharset_name] [COLLATEcollation_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.

ASET column can have a maximum of 64 distinct members.

The maximum supported length of an individualSET element isM <= 255 and (M xw) <= 1020, whereM is the element literal length andw is the number of bytes required for the maximum-length character in the character set.