mysql从头学一 1.0数据类型-字符类型

 

3.3 字符串类型

MySQL 中提供了多种对字符数据的存储类型，不同的版本可能有所差异。以 5.0 版本为例，

MySQL 包括了 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等多种字符串类型。表 3-4 中详细列出了这些字符类型的比较。

 

表 3-4                                          MySQL 中的字符类型

字符串类型	字节	描述及存储需求
CHAR（M）	M	M 为 0～255 之间的整数
VARCHAR（M）		M 为 0～65535 之间的整数，值的长度+1 个字节
TINYBLOB		允许长度 0～255 字节，值的长度+1 个字节
BLOB		允许长度 0～65535 字节，值的长度+2 个字节
MEDIUMBLOB		允许长度 0～167772150 字节，值的长度+3 个字节
LONGBLOB		允许长度 0～4294967295 字节，值的长度+4 个字节
TINYTEXT		允许长度 0～255 字节，值的长度+2 个字节
TEXT		允许长度 0～65535 字节，值的长度+2 个字节
MEDIUMTEXT		允许长度 0～167772150 字节，值的长度+3 个字节
LONGTEXT		允许长度 0～4294967295 字节，值的长度+4 个字节
VARBINARY（M）		允许长度 0～M 个字节的变长字节字符串，值的长度+1 个字节
BINARY（M）	M	允许长度 0～M 个字节的定长字节字符串

 

下面将分别对这些字符串类型做详细的介绍。

3.3.1 CHAR 和 VARCHAR 类型

CHAR 和 VARCHAR 很类似，都用来保存 MySQL 中较短的字符串。二者的主要区别在于存储方式的不同：CHAR 列的长度固定为创建表时声明的长度，长度可以为从 0～255 的任何值；而 VARCHAR 列中的值为可变长字符串，长度可以指定为 0～255（5.0.3 以前）或者 65535（5.0.3 以后）之间的值。在检索的时候，CHAR 列删除了尾部的空格，而 VARCHAR 则保留这些空格。下面的例子中通过给表 vc 中的 VARCHAR(4)和 char(4)字段插入相同的字符串来描述这个区别。

（1）创建测试表 vc，并定义两个字段 v VARCHAR(4)和 c CHAR(4)：

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));

Query OK, 0 rows affected (0.16 sec) 

（2）v 和 c 列中同时插入字符串“ab  ”：

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');

Query OK, 1 row affected (0.05 sec) 

（3）显示查询结果：

mysql> select length(v),length(c) from vc;

+-----------+-----------+

| length(v) | length(c) |

+-----------+-----------+

|         4 |         2 | 

+-----------+-----------+

1 row in set (0.01 sec)

可以发现，c 字段的 length 只有 2。给两个字段分别追加一个“+”字符看得更清楚：

mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;

+----------------+----------------+

| CONCAT(v, '+') | CONCAT(c, '+') |

+----------------+----------------+

| ab  +          | ab+            |

+----------------+----------------+

1 row in set (0.00 sec)

显然，CHAR 列最后的空格在做操作时都已经被删除，而 VARCHAR 依然保留空格。

3.3.2 BINARY 和 VARBINARY 类型

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。在下面的例子中，对表 t 中的 binary 字段 c 插入一个字符，研究一下这个字符到底是怎么样存储的。

1. 创建测试表 t，字段为 c BINARY(3)：

mysql> CREATE TABLE t (c BINARY(3)); Query OK, 0 rows affected (0.14 sec) 

1. 往 c 字段中插入字符“a”：

mysql> INSERT INTO t SET   c='a';

Query OK, 1 row affected (0.06 sec)

 

1. 分别用以下几种模式来查看 c 列的内容：

mysql> select *,hex(c),c='a',c='a\0',c='a\0\0'  from t10;

+------+--------+-------+---------+-----------+

| c    | hex(c) | c='a' | c='a\0' | c='a\0\0' |

+------+--------+-------+---------+-----------+

| a    | 610000 |     0 |       0 |         1 | 

+------+--------+-------+---------+-----------+

1 row in set (0.00 sec)

    可以发现，当保存 BINARY 值时，在值的最后通过填充“0x00”（零字节）以达到指定的字段定义长度。从上例中看出，对于一个 BINARY(3)列，当插入时'a'变为'a\0\0'。

3.3.3 ENUM 类型

 ENUM 中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对 1～ 255 个成员的枚举需要 1 个字节存储；对于 255～65535 个成员，需要 2 个字节存储。最多允许有 65535 个成员。下面往测试表 t 中插入几条记录来看看 ENUM 的使用方法。

1. 创建测试表 t，定义 gender 字段为枚举类型，成员为'M'和'F'：

mysql> create table t (gender enum('M','F'));

Query OK, 0 rows affected (0.14 sec)

1. 插入 4 条不同的记录：

mysql> INSERT INTO t  VALUES('M'),('1'),('f'),(NULL); Query OK, 4 rows affected (0.00 sec) Records: 4  Duplicates: 0  Warnings: 0  mysql> select * from t; +--------+ | gender | +--------+ | M      | | M      | | F      | | NULL   | +--------+ 4 rows in set (0.01 sec)

从上面的例子中，可以看出 ENUM 类型是忽略大小写的，对'M'、'f'在存储的时候将它们都转成了大写，还可以看出对于插入不在 ENUM 指定范围内的值时，并没有返回警告，而是插入了 enum('M','F')的第一值'M'，这点用户在使用时要特别注意。另外，ENUM 类型只允许从值集合中选取单个值，而不能一次取多个值。

3.3.4 SET 类型

     Set 和 ENUM 类型非常类似，也是一个字符串对象，里面可以包含 0～64 个成员。根据成员的不同，存储上也有所不同。

1. 1～8 成员的集合，占 1 个字节。
2. 9～16 成员的集合，占 2 个字节。
3. 17～24 成员的集合，占 3 个字节。
4. 25～32 成员的集合，占 4 个字节。
5. 33～64 成员的集合，占 8 个字节。

 

Set 和 ENUM 除了存储之外，最主要的区别在于 Set 类型一次可以选取多个成员，而 ENUM 则只能选一个。下面的例子在表 t 中插入了多组不同的成员：

Create table  t (col   set （'a','b','c','d'）; insert into t  values('a,b'),('a,d,a'),('a,b'),('a,c'),('a'); mysql> select * from t;

+------+

| col  |

+------+

| a,b  |

| a,d  |

| a,b  |

| a,c  |

| a    |

+------+

5 rows in set (0.00 sec)

SET 类型可以从允许值集合中选择任意 1 个或多个元素进行组合，所以对于输入的值只要是在允许值的组合范围内，都可以正确地注入到 SET 类型的列中。对于超出允许值范围的值例如（'a,d,f'）将不允许注入到上面例子中设置的 SET 类型列中，而对于（'a,d,a'）这样包含重复成员的集合将只取一次，写入后的结果为“a,d”，这一点请注意。