mysql有三大类数据类型, 分别为数字、日期/时间、字符串, 本文将对字符串类型进行介绍。

如下，这是mysql的字符串类型相应字段。
M：表示可存储的字符数。
L：表示整个字符串实际占用长度，不同编码集下的字符长度不同，例如Korean／Chinese／Japanese可能是3到4个字节。

名称 实际大小 范围 描述
char(M) M × w bytes
w是当前编码字符长度最大值，如utf8字符长度最大为3字节，因此CHARSET＝utf8时，存储大小为3M bytes。		 0 <= M <= 255 定长字符串
BINARY(M) M bytes 0 <= M <= 255 定长二进制
VARCHAR(M) L + B bytes
B负责记录字符串长度，如果字符串长度在255 bytes内则B为1 bytes，超出则为2 bytes。		 0 <= M <= 65535 字符串
VARBINARY(M) L + B bytes
B负责记录字符串长度，如果字符串长度在255 bytes内则B为1 bytes，超出则为2 bytes。		 0 <= M <= 65535 二进制
TINYTEXT L + 1 bytes L < 28 小字符串
TINYBLOB L + 1 bytes L < 28 小二进制
TEXT L + 1~2 bytes L < 216 字符串
BLOB L + 1~2 bytes L < 216 二进制
MEDIUMTEXT L + 1~3 bytes L < 224 中字符串
MEDIUMBLOB L + 1~3 bytes L < 224 中二进制
LONGTEXT L + 1~4 bytes L < 232 长字符串
LONGBLOB L + 1~4 bytes L < 232 长二进制
  • char和varchar

    • char是定长字段类型，因此长度大小在创建时就已固定，当存储内容长度不足时会自动用空格补齐。
    varchar不是定长，存储空间是动态的，因此要有专门的空间用来记录当前内容长度大小。正如表格所讲，如果字符串长度在0到255之间则只需要一个字节纪录即可，超过则需要两个字节纪录。

    Value CHAR(4) Storage VARCHAR(4) Storage
    '' ' ' 4 bytes '' 1 byte
    'ab' 'ab ' 4 bytes 'ab' 3 bytes
    'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
    'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

    正因为char类型是用空格补全，因此如果我们存储了结尾带空格字符串到char字段中，那么获取出来的值将会丢失空格，而varchar则不会。

    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)

    mysql有很多种collations（也叫字符序，在排序和对比时会用到），而它们都是用空格进行又填充的。CHAR／VARCHAR／TEXT存储的内容，在字符比较中会忽略右边的空格，这就导致了在where查找中会忽略右空格的差异。不过对于like语句，触发的是正则引擎分析，因此就不再受空格影响。

    mysql> CREATE TABLE names (myname CHAR(10));
    Query OK, 0 rows affected (0.03 sec)

    mysql> INSERT INTO names VALUES ('Monty');
    Query OK, 1 row affected (0.00 sec)

    mysql> SELECT myname = 'Monty', myname = 'Monty  ' FROM names;
    +------------------+--------------------+
    | myname = 'Monty' | myname = 'Monty  ' |
    +------------------+--------------------+
    |                1 |                  1 |
    +------------------+--------------------+
    1 row in set (0.00 sec)

    mysql> SELECT myname LIKE 'Monty', myname LIKE 'Monty  ' FROM names;
    +---------------------+-----------------------+
    | myname LIKE 'Monty' | myname LIKE 'Monty  ' |
    +---------------------+-----------------------+
    |                   1 |                     0 |
    +---------------------+-----------------------+
    1 row in set (0.00 sec)

    同理，对于插入语句，如果char或者varchar是unique唯一，那么你插入了‘a’，就不能再插入‘a  ’了，因为插入前mysql会先查找是否已存在，而查找就会忽略右空格，被认为已存在而无法插入。

    mysql> CREATE TABLE names2 (myname VARCHAR(10) unique);
    Query OK, 0 rows affected (0.00 sec)

    mysql> INSERT INTO names2 VALUES ('RANSHY');            
    Query OK, 1 row affected (0.01 sec)

    mysql> INSERT INTO names2 VALUES ('RANSHY ');
    ERROR 1062 (23000): Duplicate entry 'RANSHY ' for key 'myname'
  • binary和varbinary

    • 对于binary和varbinary，我们可以参考char和varchar的对比，binary像char，而varbinary像varchar。
    不过由于它们存储的是二进制，所以它们没有字符集概念（character set），不支持排序（sorting），也不支持比较（comparison）。
    binary是定长，存储时为了保证长度一定，会在存储内容的右侧用\0（空格在二进制中代表\0）补齐，例如BINARY(3) column，存入'a '（a \0），存入后将会变成'a\0\0'。这一点要牢记，在进行where语句匹配时需要手动补全，否则将搜索不到。

    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)
  • text和varchar

    • text： fixed max size of 2¹⁶-1 = 65535 characters.
    varchar： variable max size M up to M = 2¹⁶-1.

    我们不能给text字段建立索引，因此对于索引需求的字段需要使用varchar。值得注意的是，mysql索引长度是有限制的，如果varchar长度过长，那么mysql会截取一部分内容作为索引和排序。

    如果我们的需求是存储超过65535大小的文本内容，那么我们需要选择mediumtext（最大16MB）或者longtext（最大4GB）。不过在一般情况下，我们很少使用这么大的存储类型，像php这类语言调用mysql接口获取数据时也会报错提示内存不够。

    text字段类型不支持默认值，如果有需求非NULL默认值，则建议使用varchar。

    text内容并不存储在当前表中，而是存储在磁盘其它地方。

    text和varchar都会用额外存储空间纪录文本大小，额外存储空间视字段长度而定，例如：
    "Test"在varchar(30)／varchar(100)／tinytext中，占用5 bytes。
    "Test"在varchar(2000)／text中，占用6 bytes。

    每次新建数据库时，很多人都不清楚varchar数字填多少合适（1 byte原则）：


    VARCHAR(85) COLLATE UTF8_GENERAL_CI
    VARCHAR(255) COLLATE LATIN1_GENERAL_CI

  • mysql最大长度

    • mysql有的表有最大行长度限制，而所有字段（text／blob等除外）的大小加起来不能超过这个限制（具体限制视数据库类型而定）。

    上面有说VARCHAR(M)中的M指的是字符，不是字节。因此，我们其实很难使用VARCHAR(65532) utf8这种字段，它已经超出了最大行长度限制，一般插入时会报两种常见错误：
    ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs.
    ERROR 1074 (42000): Column length too big for column 'col_name' (max=[max number here]); use BLOB or TEXT instead

  • mysql简单汇总

    • 1. 当表中有text字段且不需要获取时，尽量少用select ＊，因此text内容需要从表的其它地方获取，增加耗时。
    2. 需要给字段设置非NULL默认值，请使用varchar或者char。
    3. 存储内容超过64 Kb, 可以用 MEDIUMTEXT／LONGTEXT字段。
    4. VARCHAR(255) 可以存储255个字符，实际大小很可能超过了255 bytes。
    5. 平时工作经常用到utf8的数据表，VARCHAR(85)是很好的选择。
    6. 熟记各种数据类型的补全方式，避免业务中因为空格导致的bug。