MySQL-String-Typ

String-Typen beziehen sich auf CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM und SET. In diesem Abschnitt wird beschrieben, wie diese Typen funktionieren und wie sie in Abfragen verwendet werden.

1. CHAR- und VARCHAR-Typen

CHAR- und VARCHAR-Typen sind ähnlich, werden jedoch auf unterschiedliche Weise gespeichert und abgerufen. Sie unterscheiden sich auch hinsichtlich ihrer maximalen Länge und ob nachgestellte Leerzeichen erhalten bleiben. Während der Speicherung oder des Abrufs wird keine Fallkonvertierung durchgeführt.

Die für die Typen CHAR und VARCHAR deklarierte Länge gibt die maximale Anzahl von Zeichen an, die Sie speichern möchten. CHAR(30) kann beispielsweise 30 Zeichen belegen.

Die Länge der CHAR-Spalte ist auf die Länge festgelegt, die beim Erstellen der Tabelle angegeben wurde. Die Länge kann ein beliebiger Wert zwischen 0 und 255 sein. Wenn Sie CHAR-Werte speichern, füllen Sie diese rechts mit Leerzeichen bis zur angegebenen Länge auf. Wenn ein CHAR-Wert abgerufen wird, werden nachgestellte Leerzeichen entfernt. Während der Speicherung oder des Abrufs wird keine Fallkonvertierung durchgeführt.

Die Werte in der VARCHAR-Spalte sind Zeichenfolgen variabler Länge. Die Länge kann als Wert zwischen 0 und 65.535 angegeben werden. (Die maximale effektive Länge von VARCHAR wird durch die maximale Zeilengröße und den verwendeten Zeichensatz bestimmt. Die maximale Gesamtlänge beträgt 65.532 Byte.)

Im Vergleich zu CHAR wird beim Speichern des VARCHAR-Werts nur die erforderliche Anzahl von Zeichen plus ein Byte zum Aufzeichnen der Länge gespeichert (wenn die deklarierte Länge der Spalte 255 überschreitet, werden zwei Bytes verwendet).

VARCHAR-Werte werden ohne Auffüllung gespeichert. Nachgestellte Leerzeichen bleiben beim Speichern und Abrufen des Werts erhalten, entsprechend Standard-SQL.

Wenn der einer CHAR- oder VARCHAR-Spalte zugewiesene Wert die maximale Länge der Spalte überschreitet, wird der Wert passend zugeschnitten. Wenn das abgeschnittene Zeichen kein Leerzeichen ist, wird eine Warnung generiert. Wenn Nicht-Leerzeichen gekürzt werden, führt dies zu einem Fehler (anstelle einer Warnung) und verhindert das Einfügen von Werten durch Verwendung des strikten SQL-Modus.

Die folgende Tabelle zeigt die Ergebnisse nach dem Speichern verschiedener Zeichenfolgenwerte in den Spalten CHAR(4) und VARCHAR(4) und veranschaulicht den Unterschied zwischen CHAR und VARCHAR:

Werte CHAR(4) Speicherbedarf VARCHAR(4) Speicherbedarf

'' ' ' ' 4 Bytes '' 1 Byte

'ab' 'ab ' 4 Bytes 'ab ' 3 Bytes

'abcd' 'abcd' 4 Bytes 'abcd' 5 Bytes

'abcdefgh' 'abcd' 4 Bytes 'abcd' 5 Wörter Abschnitt

Bitte beachten Sie, dass der Wert in der letzten Zeile in der Die obige Tabelle gilt nur, wenn der strikte Modus nicht verwendet wird. Wenn MySQL im strikten Modus ausgeführt wird, werden Werte, die die Spaltenlänge überschreiten, nicht gespeichert und es tritt ein Fehler auf.

Die aus den Spalten CHAR(4) und VARCHAR(4) abgerufenen Werte sind nicht immer gleich, da nachgestellte Leerzeichen beim Abrufen aus der Spalte CHAR entfernt werden. Der Unterschied wird durch das folgende Beispiel veranschaulicht:

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)

Sortiert und vergleicht Werte in CHAR- und VARCHAR-Spalten gemäß den den Spalten zugewiesenen Zeichensatz-Sortierungsregeln.

Bitte beachten Sie, dass alle MySQL-Sortierungsregeln zur PADSPACE-Klasse gehören. Dies bedeutet, dass alle CHAR- und VARCHAR-Werte in MySQL beim Vergleich keine nachgestellten Leerzeichen berücksichtigen müssen. Zum Beispiel:

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)

Bitte beachten Sie, dass dies für alle MySQL-Versionen gilt und nicht vom SQL-Servermodus betroffen ist.

In Fällen, in denen nachfolgende Füllzeichen beim Vergleich abgeschnitten oder ignoriert werden und der Spaltenindex einen eindeutigen Wert erfordert, fügen Sie einen Wert in die Spalte ein, der sich nur in der Anzahl der Füllzeichen unterscheidet verursacht einen Fehler beim Kopieren des Schlüsselwerts.

CHAR BYTE ist ein Alias ​​für CHAR BINARY. Dies dient der Gewährleistung der Kompatibilität.

Das ASCII-Attribut weist der CHAR-Spalte den Zeichensatz latin1 zu. Das UNICODE-Attribut weist den ucs2-Zeichensatz zu.

2. BINARY- und VARBINARY-Typen

Die BINARY- und VARBINARY-Klassen ähneln CHAR und VARCHAR, außer dass sie binäre Zeichenfolgen anstelle von nicht-binären Zeichenfolgen enthalten. Das heißt, sie enthalten Byte-Strings und keine Zeichenfolgen. Dies bedeutet, dass sie keinen Zeichensatz haben und Sortierung und Vergleich auf dem numerischen Wert der Spaltenwertbytes basieren.

Die maximal zulässigen Längen von BINARY und VARBINARY sind dieselben, genau wie CHAR und VARCHAR. Der Unterschied besteht darin, dass die Länge von BINARY und VARBINARY die Länge der Bytes und nicht die Länge der Zeichen ist.

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定义。