MySql 支持的字符串类型如下表:

Char 与 Varchar

相同点:

  • CharVarchar在定义时都指定了能存储的最大的字符数,例如:char(30)可以存储 30 个字符。
  • 当你给CharVarchar赋的值超过其有效长度时,如果处在strict sql mode, 那么将不能成功插入数据,如果没处在strict mode,那么数据将会发生裁剪,并且系统会发出警告。
  • CharVarchar的值的比较都根据它们的Collation属性来进行的,比较的时候,会忽略掉所有右补的空格,例如 'Hello ' = 'Hello'是成立的。要注意的是,like语句不会忽略右补的空格,例如 'hello ' like 'hello'不成立!详情可以看以下代码:
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)
  • 如果一个字符串的列要求唯一的值,并且已经有了值’a’,这个时候,要插入’a ‘,也是还是会导致值重复的错误的。

不同点:

  • Char 是定长的,它的长度固定为你创建表格时声明的长度,有效值从 0255之间,当你存储一个 char类型的值时,系统会自动在右边补空格,当获取该值时,系统再自动把右边的空格去掉1
  • Varchar 是不定长的,它的长度范围在065535之间。要注意的是它的有效范围还取决于最大行大小,以及该列使用的字符编码集。它不会有自动右边空格的情况发生。

Binary 与 Varbinary

BinaryVarbinary 类似 CharVarchar,只是它们包含的往往是字节数据,而不是字符数据,所以它们的字符编码集以及 collation 是binary。它们的排序与比较都是基于它们的字节数据。

最大允许的长度也和 charvarchar一样,只是它们的单位是字节而不是字符

BinaryVarbinaryChar BinaryVarchar Binary是不同的。后者只会给字符设置一个_bin的 collation,例如:Char(5) Binary会被当做是Char(5) Character set latin1 collate latin1_bin,而Binary(5)则是一个有binary charset的字节串。

CharVarchar一样,当你给BinaryVarbinary赋的值超过其有效长度时,如果处在strict sql mode, 那么将不能成功插入数据,如果没处在strict mode,那么数据将会发生裁剪,并且系统会发出警告。

Binary储存值会自动右补'\0'至声明的长度,(Char是右补空格),并且与Char不同的是,Binary在select的时候不会把右补的'\0'去除。Varbinary不会有这个特性

如果你需要某个值获取出来的长度跟存储的长度一致,那么你最好使用Varbinary或者Blob,因为这两种类型没有自动右补'\0'的特性。

Blob 与 Text

Blob是一个可以存储可变长度大字节块的类型,分别包含TinyBlobBlobMediumBlob以及LongBlob。它们的区别在于能保存的数据的长度。

Text是一个可以存储可变长度大段文字的类型,分别包含TinyTextTextMediumText以及LongText。它们的区别,与Blob一样,只在于能保存的数据的长度。

Blob可以看做是字节串,它的内容的 charset 与 collation 都是binary的(即它的值的排序与存储基于它们的字节数值)而Text可以看做是字符串,它们含有非binary的 charset 有 collation。

当你给BlobText赋的值超过其有效长度时,如果处在strict sql mode, 那么将不能成功插入数据,如果没处在strict mode,那么数据将会发生裁剪,并且系统会发出警告。

BlobText不会发生任何后补空格去掉后补空格的状况。

如果当前键是唯一的,并且是Text类型。如果尝试插入的数据跟已有数据的差别仅仅是后补空格,那么将会引发键重复错误,比如表中已有唯一键'a',尝试插入'a ',会报错。Blob则不会出现这种情况。

BlobText不能含有Default

因为BlobText可以非常的长,所以使用它们时有几点限制需要注意:

  • 只有指定列的前max_sort_length字节会参与到排序中,默认的max_sort_length的值是1024,你可以自己指定该数值,所以客户都可以改变自己 session 对应的这个值。

    set max_sort_length = 2000;
    select id, comment from t order by comment;
    
  • 如果 select 的结果集包含了类型为BlobText的列,那么会导致产生临时表,所以请在确实必要的时候才把 BlobText 加入结果集。(在 select *的时候可要注意一下了)。

Enum

Enum(枚举)类型的优点是:

  • 如果结果是一个数量有限的字符串的集合,那么使用Enum可能会更加节省空间(因为Enum内部是编码成数字保存的)。
  • 在节省空间的同时不用担心查询结果的可读性,因为在查询时会自动把内部存储的数字索引转换为对应的字符串。

但是使用Enum时有几点需要注意:

  • Enum必须是字符串字面量。例如,你要这样创建枚举:

    CREATE TABLE shirts (
    	name VARCHAR(40),
        size ENUM('xs', 's', 'm', 'l', 'xl')
    );
    INSERT INTO shirts (name, size) values 
    	('dress shirts', 'xl'), 
    	('t-shirt', 'm');
    SELECT name, size FROM shirts WHERE size = 'm';
    +---------+--------+
    | name    | size   |
    +---------+--------+
    | t-shirt |   m    |
    +---------+--------+
    UPDATE shirts SET size = 'm' WHERE size = 'xl';
    COMMIT;
    
  • 枚举类型的数字索引

    每个枚举项都有自己的对应的数字索引:

    • 每个元素的数字索引从1开始
    • 代表非法字符的空字符串的数字索引是0(如果插入了不合法的枚举值,在非 strict 模式下会被转换为一个特殊空字符串,这个字符串的数字索引永远是0,而如果是正常合法的空字符串,它的数字索引值肯定>=1
    • NULL的数字索引是 NULL

    例如一个枚举 Enum('Mercury', 'Venus', 'Earth')可以试下表的值:

    数字索引
    NULL NULL
    ’’ 0
    ‘Mercury’ 1
    ‘Venus’ 2
    ‘Earth’ 3

    一个Enum可以有65,535种不同的元素,一个表可以有超过255个不同的 EnumSet元素定义表。

    如果你在一个需要数字的上下文获取一个Enum类型,那么会发生隐式的转换,比如你可以这样获取一个Enum列的数字索引

    SELECT enum_col+0 FROM tbl_name;
    

    函数如SUM()AVG()等接受数字作为参数的函数也会自动把Enum转换为它对应的数字索引。

  • 定义Enum时字符串后面连续的空格会被自动地删除掉,查询时,枚举类型显示的结果的大小写与你定义表的时候定义的大小写一致,注意你插入记录的时候,枚举类型的列是不区分大小写的。

  • 要查看所有可选的枚举项,可以使用SHOW COLUMNS FROM tbl_name like 'enum_col',结果集中的Type字段记录了可选的枚举项。

  • 如果一个Enum列允许NULL值,那么这个列的默认值就是NULL,如果这个列不允许NULL值,那么这个列的默认值是它的枚举项的第一项。

  • 非常不建议使用数字字符串作为Enum类型的枚举项。

Set

Set表示一个集合中的0到多个元素,比如一个SET('one', 'Two') not null可以有以下几种可能的取值:

''
'one'
'two'
'one,two'

一个Set可以有64个不同的元素,这是因为在内部,一个 Set 用一个64位的整数表示的,其中每一个元素就是一个位,它们并起来就是最终的值。比如一个SET('a', 'b', 'c', 'd'),这几个元素有以下的值:

Set 元素 十进制值 二进制值
‘a’ 1 0001
‘b’ 2 0010
‘c’ 3 0100
‘d’ 4 1000

所以不管你插入记录时,Set 的相同的元素重复了多个,或者顺序如何,最终并起来的结果都是一样的。比如"A,D""D,A,D"的值是相同的。

有以下几种方法来查询一个Set类型:

SELECT * FROM tbl_name WHERE FIND_IN_SET('value', set_col)>0;
SELECT * FROM tbl_name WHERE set_col like 'value';
SELECT * FROM tbl_name WHERE set_col & 1;
SELECT * FROM tbl_name WHERE set_col = 'value';

要查看所有的 Set 的元素,可以使用SHOW COLUMNS FROM tbl_name LIKE set_col,结果集中的Type字段记录了该Set的元素。

  1. 启用 PAD_CHAR_TO_FULL_LENGTH这个模式可以禁止该特性。