MySQL中的数据类型

190次阅读

共计 4648 个字符，预计需要花费 12 分钟才能阅读完成。

一、char 和 varchar 类型

char 和 varchar 类型类似，都用来存储字符串，但是它们保存和检索字符串的方式不同。char 属于固定长度的字符类型，varchar 属于可变长度的字符类型。例如：对于 char(4) 和 varchar(4) 这两种类型定义来说：

(1)、” 在 char(4) 中占了 4 个字节长度，varchar(4) 则只占用一个字节的长度；

(2)、’ab’ 在 char(4) 中占了 4 个字节长度，varchar(4) 中则只占用了 3 个字节的长度；

(3)、’abcd’ 在 char(4) 中占用了 4 个字节长度，在 varchar(4) 中则占用了 5 个字节的长度；

为何在 varchar 类型中会多出一个字节长度呢？这是因为 varchar 类型将这多出的一个字节用于保存 varchar 类型实际使用了多大的长度。char(4) 和 varchar(4) 的检索并不总是相同的，例如：

MySQL> create table char_and_varchar (v varchar(4),c char(4));
Query OK, 0 rows affected (0.20 sec)
 
mysql> insert into char_and_varchar values ('ab','ab');
Query OK, 1 row affected (0.33 sec)
 
mysql> select concat(v,'cd'),concat(c,'cd') from char_and_varchar;
+----------------+----------------+
| concat(v,'cd') | concat(c,'cd') |
+----------------+----------------+
| ab  cd        | abcd          |
+----------------+----------------+
1 row in set (0.35 sec)

由于 char 是固定长度的，所以它的处理速度比 varchar 快的多，但其缺点是浪费存储空间，程序需要对尾部空格进行处理等缺点，所以多那些长度变化不大并且对查询速度有较高要求的的数据可以考虑使用 char 类型来存储。随着 MySQL 版本的不断升级，varchar 数据类型的性能也将不断提升，varchar 类型的应用范围更加广泛。

在 MySQL 中，不同的存储引擎对 char 和 varchar 的使用原则有所不同：

(1)、在 MyISAM 存储引擎中，建议使用固定长度的字段类型代替可变长度的字段类型。
(2)、在 Memory 存储引擎中，目前都是用固定长度的数据行存储，因此无论是 char 还是 varchar 类型，都将转化为 char 类型处理。
(3)、在 InnoDB 存储引擎中，建议使用 varchar 类型。

二、TEXT 和 BLOB

在保存少量字符串的时候，可以使用 char 和 varchar 数据类型。在保存较大的文本时，通常会选择使用 text 或 BLOB。两者之间的主要差别是：BLOB 能用来保存二进制数据，例如：照片，而 text 只能用于保存字符类型数据。text 和 BLOB 中又分别包括 text、mediumtext、longtext 和 blob、mediumblob、longblob 三种不同的类型。它们之间的主要区别是存储文本的长度不同和存储字节不同。

使用 BLOB 和 TEXT 类型应注意的一些问题：

(1)、BLOB 和 TEXT 会引起一些性能问题，特别是在执行了大量的删除操作时。删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入性能上会有影响。为了提高性能，应定期使用 OPTIMIZETABLE 功能对这类表进行碎片整理，避免空洞导致性能问题。

(2)、使用合成的索引来提高大本文字段的查询性能。所谓合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，然后就可以通过散列值找到数据行了。例如：

mysql> create table t (id varchar(100),content blob,hash_value varchar(40));
Query OK, 0 rows affected (0.03 sec)
 
mysql> insert into t values (1,repeat('beijing',2),md5(content)); 
Query OK, 1 row affected (0.33 sec)
 
mysql> insert into t values (2,repeat('beijing',2),md5(content)); 
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into t values (2,repeat('beijing 2008',2),md5(content));
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from t;
+------+--------------------------+----------------------------------+
| id  | content                  | hash_value                      |
+------+--------------------------+----------------------------------+
| 1    | beijingbeijing          | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijingbeijing          | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+------+--------------------------+----------------------------------+
3 rows in set (0.00 sec)
 
mysql> select * from t where hash_value=md5(repeat('beijing 2008',2));
+------+--------------------------+----------------------------------+
| id  | content                  | hash_value                      |
+------+--------------------------+----------------------------------+
| 2    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+------+--------------------------+----------------------------------+
1 row in set (0.00 sec)

合成索引只能用于精确匹配的场景，在一定程度上减少了磁盘 I /O，提高了查询效率。如果需要对 BLOB、CLOB 字段进行模糊查询，可以使用 MySQL 的前缀索引，即为字段的前 n 列创建索引。例如：

mysql> create index idx_blob on t (content(100));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show index from t \G
*************************** 1. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_blob
 Seq_in_index: 1
  Column_name: content
    Collation: A
  Cardinality: 3
     Sub_part: 100
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
 
mysql> desc select * from t where content like 'beijing%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: idx_blob
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

(3)、不要在不必要是检索大型的 BLOB 或 TEXT 字段。

(4)、把 BLOB 或 TEXT 字段分离到单独的表中。

三、浮点数和定点数

浮点数一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型以后，如果插入数据的精度超过了该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。MySQL 中的 float、double（real）用来表示浮点数。

定点数不同于浮点数，定点数实际上是用字符串形式存放的，所以定点数可以更精确的存放数据。如果插入数据的精度大于实际定义的精度，则 MySQL 会发出告警，但数据按照实际精度四舍五入后插入（如果是在传统模式下插入，则会报错）。在 MySQL 中，用 decimal（或 numberic）来表示定点数。

用浮点数存储数据会存在误差，在精度要求比较高的场景（如货币），应该使用定点数来存放数据。例如：

mysql> create table b (c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.37 sec)
 
mysql> insert into b values (131072.32,131072.32);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from b;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
1 row in set (0.00 sec)