mysqlの文字列型
- 伊谢尔伦オリジナル
- 2016-11-23 13:17:341854ブラウズ
文字列型とは、CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET を指します。このセクションでは、これらの型がどのように機能するか、およびクエリでの使用方法について説明します。
1. CHAR 型と VARCHAR 型
CHAR 型と VARCHAR 型は似ていますが、保存方法と取得方法が異なります。また、最大長と末尾のスペースが保持されるかどうかという点でも異なります。保存または取得中に大文字と小文字の変換は実行されません。
CHAR 型と VARCHAR 型の宣言された長さは、保存する最大文字数を示します。たとえば、CHAR(30) は 30 文字を占めることができます。
CHARカラムの長さはテーブル作成時に宣言した長さに固定されます。長さは 0 ～ 255 の任意の値にすることができます。 CHAR 値を保存するときは、指定した長さまで右側にスペースを埋め込みます。 CHAR 値を取得すると、末尾のスペースが削除されます。保存または取得中に大文字と小文字の変換は実行されません。
VARCHAR 列の値は可変長文字列です。長さは 0 ～ 65,535 の値で指定できます。 (VARCHAR の最大有効長は、最大行サイズと使用される文字セットによって決まります。全体の最大長は 65,532 バイトです)。
CHAR と比較して、VARCHAR 値が保存される場合、必要な数の文字のみが保存され、さらに長さを記録するために 1 バイトが保存されます (列の宣言された長さが 255 を超える場合は、2 バイトが使用されます)。
VARCHAR 値はパディングなしで保存されます。標準 SQL に準拠して、値の保存および取得時に末尾のスペースが保持されます。
CHAR または VARCHAR 列に割り当てられた値が列の最大長を超える場合、値は収まるようにクリップされます。切り捨てられた文字がスペースでない場合は、警告が生成されます。スペース以外の文字がトリミングされると、(警告ではなく) エラーが発生し、厳密な SQL モードを使用した値の挿入が無効になります。
以下の表は、さまざまな文字列値を CHAR(4) 列と VARCHAR(4) 列に保存した後の結果を示し、CHAR と VARCHAR の違いを示しています。
値 CHAR(4) 記憶域要件 VARCHAR( 4) 記憶域要件
'' '' 4 バイト '' 1 バイト
'ab' 4 バイト 'ab' 3 バイト
'abcd' 'abcd' 4 ワード セクション 'abcd' 5 バイト
'abcdefgh' 'abcd' 4 バイト'abcd' 5 バイト
上記の表の最後の行の値は、厳密モードが使用されていない場合にのみ適用されることに注意してください。MySQL が厳密モードで実行されている場合、列の長さを超える値は保存されません。エラーが発生します。
CHAR(4) 列と VARCHAR(4) 列から取得される値は、取得時に CHAR 列から末尾のスペースが削除されるため、必ずしも同じではありません。違いを次の例で示します。
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));Query OK, 0 rows affected (0.02 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.00 sec)
CHAR 列と VARCHAR 列の値を、列に割り当てられた文字セットの照合規則に従って並べ替えて比較します。
すべての MySQL 照合ルールは PADSPACE クラスに属していることに注意してください。これは、MySQL のすべての CHAR 値と VARCHAR 値を比較するときに、末尾のスペースを考慮する必要がないことを意味します。例:
mysql> CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO names VALUES ('Monty ', 'Monty ');Query OK, 1 row affected (0.00 sec) mysql> SELECT myname = 'Monty ', yourname = 'Monty ' FROM names; +--------------------+----------------------+ | myname = 'Monty ' | yourname = 'Monty ' | +--------------------+----------------------+ | 1 | 1 | +--------------------+----------------------+ 1 row in set (0.00 sec)
これはすべての MySQL バージョンに当てはまり、SQL サーバー モードの影響を受けないことに注意してください。
比較中に末尾のパディング文字が切り取られるか無視される状況で、列のインデックスに一意の値が必要な場合、パディング文字の数だけが異なる値を列に挿入すると、コピーキーが発生します。エラー。
CHAR BYTE は CHAR BINARY の別名です。これは互換性を確保するためです。
ASCII 属性は、latin1 文字セットを CHAR 列に割り当てます。 UNICODE 属性は、ucs2 文字セットを割り当てます。
2. BINARY および VARBINARY 型
BINARY クラスと VARBINARY クラスは、非バイナリ文字列ではなくバイナリ文字列を含むことを除いて、CHAR および VARCHAR と似ています。つまり、文字列ではなくバイト列が含まれます。これは、文字セットがなく、並べ替えと比較が列値バイトの数値に基づいて行われることを意味します。
BINARY と VARBINARY の最大許容長は、CHAR と VARCHAR と同様に同じです。違いは、BINARY と VARBINARY の長さが文字の長さではなくバイトの長さであることです。
BINARY和VARBINARY数据类型不同于CHAR BINARY和VARCHAR BINARY数据类型。对于后一种类型，BINARY属性不会将列视为二进制字符串列。相反，它致使使用列字符集的二元 校对规则，并且列自身包含非二进制字符字符串而不是二进制字节字符串。例如CHAR(5) BINARY被视为CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin，假定默认字符集是latin1。这不同于BINARY(5)，它保存5字节二进制字符串，没有字符集或 校对规则。
当保存BINARY值时，在它们右边填充值以达到指定长度。填充值是0x00(零字节)。插入值时在右侧添加0x00 on，并且选择时不删除尾部的字节。比较时所有字节很重要，包括ORDER BY和DISTINCT操作。比较时0x00字节和空格是不同的，0x00<空格。
例如：对于一个BINARY(3)列，当插入时 'a' 变为 'a \0'。'a\0'插入时变为'a\0\0'。当选择时两个插入的值均不更改。
对于VARBINARY，插入时不填充字符，选择时不裁剪字节。比较时所有字节很重要，包括ORDER BY和DISTINCT操作。比较时0x00字节和空格是不同的，0x00<空格。
对于尾部填充字符被裁剪掉或比较时将它们忽视掉的情形，如果列的索引需要唯一的值，在列内插入一个只是填充字符数不同的值将会造成复制键值错误。
如果你计划使用这些数据类型来保存二进制数据并且需要检索的值与保存的值完全相同，应考虑前面所述的填充和裁剪特征。下面的例子说明了用0x00填充的BINARY值如何影响列值比较：
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)
如果检索的值必须与指定进行存储而没有填充的值相同，最好使用BLOB数据类型。
创建表时，MySQL可以默默更改BINARY或VARBINARY列的类型。
3. BLOB和TEXT类型
BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。
BLOB 列被视为二进制字符串(字节字符串)。TEXT列被视为非二进制字符串(字符字符串)。BLOB列没有字符集，并且排序和比较基于列值字节的数值值。TEXT列有一个字符集，并且根据字符集的 校对规则对值进行排序和比较。
在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。
当未运行在严格模式时，如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值，值被截取以保证适合。如果截掉的字符不是空格，将会产生一条警告。使用严格SQL模式，会产生错误，并且值将被拒绝而不是截取并给出警告。
在大多数方面，可以将BLOB列视为能够足够大的VARBINARY列。同样，可以将TEXT列视为VARCHAR列。BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHAR：
· 当保存或检索BLOB和TEXT列的值时不删除尾部空格。(这与VARBINARY和VARCHAR列相同）。
请注意比较时将用空格对TEXT进行扩充以适合比较的对象，正如CHAR和VARCHAR。
· 对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。
· BLOB和TEXT列不能有 默认值。
LONG和LONG VARCHAR对应MEDIUMTEXT数据类型。这是为了保证兼容性。如果TEXT列类型使用BINARY属性，将为列分配列字符集的二元 校对规则。
MySQL连接程序/ODBC将BLOB值定义为LONGVARBINARY，将TEXT值定义为LONGVARCHAR。
由于BLOB和TEXT值可能会非常长，使用它们时可能遇到一些约束：
· 当排序时只使用该列的前max_sort_length个字节。max_sort_length的 默认值是1024；该值可以在启动mysqld服务器时使用--max_sort_length选项进行更改。
运行时增加max_sort_length的值可以在排序或组合时使更多的字节有意义。任何客户端可以更改其会话max_sort_length变量的值：
mysql> SET max_sort_length = 2000;mysql> SELECT id, comment FROM tbl_name -> ORDER BY comment;
当你想要使超过max_sort_length的字节有意义，对含长值的BLOB或TEXT列使用GROUP BY或ORDER BY的另一种方式是将列值转换为固定长度的对象。标准方法是使用SUBSTRING函数。例如，下面的语句对comment列的2000个字节进行排序：
mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name -> ORDER BY SUBSTRING(comment,1,2000);
· BLOB或TEXT对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序。例如，可以使用 mysql和mysqldump来更改客户端的max_allowed_packet值。
每个BLOB或TEXT值分别由内部分配的对象表示。这与其它列类型形成对比，后者是当打开表时为每1列分配存储引擎。
4. ENUM类型
ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。
在某些情况下，ENUM值也可以为空字符串('')或NULL：
· 如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同，该字符串有数值值0。后面有详细讨论。
· 如果将ENUM列声明为允许NULL，NULL值则为该列的一个有效值，并且 默认值为NULL。如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素。
每个枚举值有一个索引：
· 来自列规定的允许的值列中的值从1开始编号。
· 空字符串错误值的索引值是0。这说明你可以使用下面的SELECT语句来找出分配了非法ENUM值的行：
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
· NULL值的索引是NULL。
例如，定义为ENUM的列('one'，'two'，'three')可以有下面所示任何值。还显示了每个值的索引：
值 索引
NULL NULL
'' 0
'one' 1
'two' 2
'three' 3
枚举最多可以有65,535个元素。
当创建表时，ENUM成员值的尾部空格将自动被删除。
当检索时，保存在ENUM列的值使用列定义中所使用的大小写来显示。请注意可以为ENUM列分配字符集和 校对规则。对于二进制或大小写敏感的校对规则，当为列分配值时应考虑大小写。
如果在数值上下文中检索一个ENUM值，将返回列值的索引。例如，你可以这样从ENUM列搜索数值：
mysql> SELECT enum_col+0 FROM tbl_name;
如果将一个数字保存到ENUM列，数字被视为索引，并且保存的值是该索引对应的枚举成员。(但是，这不适合LOAD DATA，它将所有输入视为字符串）。不建议使用类似数字的枚举值来定义一个ENUM列，因为这很容易引起混淆。例如，下面的列含有字符串值'0'、'1'和'2'的枚举成员，但数值索引值为1、2和3：
numbers ENUM('0','1','2')
根据枚举成员在列定义中列出的顺序对ENUM值进行排序。(换句话说，ENUM值根据索引编号进行排序）。例如，对于ENUM('a'，'b')，'a'排在'b'前面，但对于ENUM('b'，'a')，'b'排在'a'前面。空字符串排在非空字符串前面，并且NULL值排在所有其它枚举值前面。要想防止意想不到的结果，按字母顺序规定ENUM列。还可以使用GROUP BY CAST(col AS CHAR)或GROUP BY CONCAT(col)来确保按照词汇对列进行排序而不是用索引数字。
如果你想要确定一个ENUM列的所有可能的值，使用SHOW COLUMNS FROM tbl_name LIKE enum_col，并解析输出中第2列的ENUM定义。
5. SET类型
SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。
例如，指定为SET('one', 'two') NOT NULL的列可以有下面的任何值：
'' 'one' 'two' 'one,two'
SET最多可以有64个不同的成员。
当创建表时，SET成员值的尾部空格将自动被删除。
当检索时，保存在SET列的值使用列定义中所使用的大小写来显示。请注意可以为SET列分配字符集和 校对规则。对于二进制或大小写敏感的校对规则，当为列分配值时应考虑大小写。
MySQL用数字保存SET值，所保存值的低阶位对应第1个SET成员。如果在数值上下文中检索一个SET值，检索的值的位设置对应组成列值的SET成员。例如，你可以这样从一个SET列检索数值值：
mysql> SELECT set_col+0 FROM tbl_name;
如果将一个数字保存到SET列中，数字中二进制表示中的位确定了列值中的SET成员。对于指定为SET('a','b','c','d')的列，成员有下面的十进制和二进制值：
SET成员 十进制值 二进制值
'a' 1 0001
'b' 2 0010
'c' 4 0100
'd' 8 1000
如果你为该列分配一个值9，其二进制形式为1001，因此第1个和第4个SET值成员'a'和'd'被选择，结果值为 'a,d'。
对于包含多个SET元素的值，当插入值时元素所列的顺序并不重要。在值中一个给定的元素列了多少次也不重要。当以后检索该值时，值中的每个元素出现一次，根据表创建时指定的顺序列出元素。例如，假定某个列指定为SET('a','b','c','d')：
mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
插入值'a,d'、'd,a'、'a,d,d'、'a,d,a'和'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
当检索时所有这些值显示为 'a,d'：
mysql> SELECT col FROM myset; +------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 5 rows in set (0.04 sec)
如果将SET列设置为一个不支持的值，则该值被忽略并发出警告：
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)
SET值按数字顺序排序。NULL值排在非NULL SET值的前面。
通常情况，可以使用FIND_IN_SET()函数或LIKE操作符搜索SET值：
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0; mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
第1个语句找出SET_col包含value set成员的行。第2个类似，但有所不同：它在其它地方找出set_col包含value的行，甚至是在另一个SET成员的子字符串中。
下面的语句也是合法的：
mysql> SELECT * FROM tbl_name WHERE set_col & 1;mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
第1个语句寻找包含第1个set成员的值。第2个语句寻找一个确切匹配的值。应注意第2类的比较。将set值与'val1,val2'比较返回的结果与同'val2,val1'比较返回的结果不同。指定值时的顺序应与在列定义中所列的顺序相同。
如果想要为SET列确定所有可能的值，使用SHOW COLUMNS FROM tbl_name LIKE set_col并解析输出中第2列的SET定义。