MySQL：设计数据库时如何选择字段的数据类型

朱雀 2022-04-23 11:28 279阅读 0赞

### Mysql性能优化 ###

*  从数据类型开始
 *   *  设计数据库时选取列数据类型的原则
     *  如何选择标志列的数据类型
     *  如果选取普通列的数据类型
     *   *  存储数字
         *   *  整数
             *  实数
         *  存储字符串
         *   *  varchar(n) 与char(n)
             *  binary(n) 和 varbinary(n)
             *  BLOB与TEXT类型
             *  字符集
         *  集合类型ENUM 和 SET
         *  BIT

# 从数据类型开始 #

## 设计数据库时选取列数据类型的原则 ##

*  在确保没有低估值的存储范围的前提下，尽可能使用 存储数据的最小数据类型
    
     *  原因：数据类型越小，处理数据越快，占用的磁盘、内存和 CPU缓存越少，处理时需要的CPU的周期越小
     *  实例：tinyint unsigned 【存储0-200之间的数据】
 *  尽可能使用简单的数据类型
    
     *  原因：更少的CPU周期，比如整形比字符串的操作代价更低，因为字符集和排序使得字符比较比整形比较更加复杂
     *  实例：应该使用Mysql内建的类型而不是字符串来存储日期和时间  
        应该使用整型存储IP地址而不是varchar(15)：因为IPv4实际是无符号整数而不是字符串，使用应该用无符号整数存储IP地址待验证
 *  尽量避免NULL：最好指定列为NOT NULL，除非真的需要存储NULL
    
     *  原因：MYSQL很难优化查询中的NULL列，因为NULL列使得索引、索引统计和值比较更为复杂。  
        NULL列会使用更多的存储空间，  
        当NULL列被索引时，每个索引记录需要一个额外的字节，在MyISAM里可能导致固定大小的索引编程可变大小索引。
     *  备注：通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以调优时没必要去改变这种情况，除非真的会导致问题
     *  例外：InnoDB使用单独的bit存储NULL值，这对于稀疏数据【大部分是NULL，只有少数非NULL】有很好的空间效率。但是MyISAM不适合

## 如何选择标志列的数据类型 ##

标志列可能会用作外键，一旦标志列的数据类型确定好了，就要确保所有关联表中都使用同样的类型，否则可能导致性能问题或者未知的错误

>  *  整数类型  
>     最好的选择：因为快而且auto\_increment
>  *  enum和set类型  
>     糟糕的选择
>  *  字符串类型  
>     尽量别使用：慢而且耗空间

## 如果选取普通列的数据类型 ##

### 存储数字 ###

#### 整数 ####

*  整数类型  
    1、它们存储值的范围从-2^(N-1) ~ 2^(N-1)-1，其中N是存储空间的位数  
    2、选择不同的类型决定怎么在内存和磁盘中保存数据，但是除了一些聚合函数使用decimal和double计算外，一般使用64位的bitint整数进行整数计算，即使在32位的环境中也是如此

<table> 
 <thead> 
  <tr> 
   <th>整数类型</th> 
   <th>存储空间</th> 
   <th>范围（有符号)</th> 
   <th>范围（无符号）</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYINT</td> 
   <td>8bit(1 字节)</td> 
   <td>(-128，127)</td> 
   <td>(0，255)</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td>16bit(2 字节)</td> 
   <td>(-32 768，32 767)</td> 
   <td>(0，65 535)</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td>24bit(3 字节)</td> 
   <td>(-8 388 608，8 388 607)</td> 
   <td>(0，16 777 215)</td> 
  </tr> 
  <tr> 
   <td>INT或INTEGER</td> 
   <td>32bit(4 字节)</td> 
   <td>(-2 147 483 648，2 147 483 647)</td> 
   <td>(0，4 294 967 295)</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td>64bit(8 字节)</td> 
   <td>(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)</td> 
   <td>(0，18 446 744 073 709 551 615)</td> 
  </tr> 
 </tbody> 
</table>

*  有符号与无符号  
    1、Mysql存储整数默认是有符号的，如果想要只存储无符号整数，需要选择UNSIGNED属性。  
    2、unsigned可以使得整数的上限提高一般，比如tinyint unsigned可以存储0-255之间的数组，而tinyint存储范围是-128到127。  
    3、有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型  
    4、一般情况下，如果选择int，推荐用有符号数，使用无符号数只是比原来多一倍得取值，数量级上没有改变。如果需要取值范围很大，直接选择用BIGINT

mysql> create table test_unsigned(a int unsigned, b int unsigned);
    
    mysql> insert into test_unsigned values(1, 2);
    
    mysql> select a - b from test_unsigned;
    ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`burn_test`.`test_unsigned`.`a` - `burn_test`.`test_unsigned`.`b`)'
    
    mysql> select b - a from test_unsigned;
    +-------+
    | b - a |
    +-------+
    |     1 |
    +-------+
    
    mysql> set sql_mode = 'no_unsigned_subtraction'; -- 这样就可以得到负数
    
    mysql> select a - b from test_unsigned;
    +-------+
    | a - b |
    +-------+
    |    -1 |
    +-------+

*  Mysql可以为整数类型指定宽度，比如INT(11)，但是这是没有意义的，对于存储和计算来说，INT(1)和INT(20)是相同的,只是会限制可以表示的值范围。当存储时使用的存储空间依然是4字节
 *  INT(N)默认是不起作用的，除非和zerofull搭配
    
     *  当存储的数据长度小于N，zerofull会用数字0填充左边然后显示，没有zerofull原样显示数据
     *  当存储的长度大于N，两个都是原样显示
 *  也就是说， INT(N)只是跟显示有关，不影响实际存储

mysql> create table test_int_n(a int(3) zerofill, b int(3));
    
    mysql> insert into test_int_n(a, b) values(1, 1);
    
    mysql> select * from test_int_n;
    +------+------+
    | a    | b    |
    +------+------+
    |  001 |    1 |
    +------+------+
    
    mysql> insert into test_int_n(a, b) values(1111, 11111);
    
    mysql> select * from test_int_n;
    +------+-------+
    | a    | b     |
    +------+-------+
    |  001 |     1 |
    | 1111 | 11111 |
    
    mysql> select a, HEX(a) from test_int_n\G
    *************************** 1. row ***************************
         a: 001
    HEX(a): 1    -- 实际存储的还是1
    *************************** 2. row ***************************
         a: 1111
    HEX(a): 457  -- 1111对应的16进制就是457
    2 rows in set (0.00 sec)

*  AUTO\_INCREMENT
    
     *  必须是索引的一部分
     *  AUTO\_INCREMENT是实例启动时，取当前表的最大值，然后 +1 即为下次自增的值。（MAX + 1)

mysql> create table test_auto_increment(a int auto_increment);
    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
    -- 没有指定为key，报错了
    
    mysql> create table test_auto_increment(a int auto_increment primary key);  -- 指定为key后有效
    
    mysql> insert into test_auto_increment values(NULL);  -- 插入NULL值
    
    mysql> select * from test_auto_increment;
    +---+
    | a |
    +---+
    | 1 |  -- 插入NULL值，便可以让其自增，且默认从1开始
    +---+
    
    mysql> insert into test_auto_increment values(0);  -- 插入 0
    
    mysql> select * from test_auto_increment;
    +---+
    | a |
    +---+
    | 1 |
    | 2 |  -- 插入 0 ，自增长为2
    +---+
    
    mysql> insert into test_auto_increment values(-1);  -- 插入 -1
    
    mysql> select * from test_auto_increment;
    +----+
    | a  |
    +----+
    | -1 |   -- 刚刚插入的-1
    |  1 |
    |  2 |
    +----+
    
    mysql> insert into test_auto_increment values(NULL);  -- 继续插入NULL
    
    mysql> select * from test_auto_increment;
    +----+
    | a  |
    +----+
    | -1 |
    |  1 |
    |  2 |
    |  3 |  -- 刚刚插入NULL， 自增为3
    +----+
    
    mysql> insert into test_auto_increment values('0'); -- 插入字符0
    
    mysql> select * from test_auto_increment;
    +----+
    | a  |
    +----+
    | -1 |
    |  1 |
    |  2 |
    |  3 |
    |  4 |  -- 插入字符'0' 后， 自增长为4
    +----+
    
    mysql> update test_auto_increment set a = 0 where a = -1;  -- 更新为0
    Query OK, 1 row affected (0.03 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from test_auto_increment;
    +---+
    | a |
    +---+
    | 0 |  -- 原来的 -1 更新为0
    | 1 |
    | 2 |
    | 3 |
    | 4 |
    +---+
    
    --
    --  数字 0 这个值比较特殊， 插入0和插入NULL的效果是一样的，都是代表自增
    --
    
    -----
    
    mysql> insert into test_auto_increment values(NULL), (100), (0);
    Query OK, 3 rows affected (0.02 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select * from test_auto_increment;
    +-----+
    | a   |
    +-----+
    |   0 |
    |   1 |
    |   2 |
    |   3 |
    |   4 |
    |   5 | -- 第一个NULL
    | 100 | -- 100
    | 101 | -- 0, 按当前最大的值 +１来设置，之前是100，所以这里101
    +-----+
    
    mysql> insert into test_auto_increment values(99); -- 插入99
    
    mysql> select * from test_auto_increment;
    +-----+
    | a   |
    +-----+
    |   0 |
    |   1 |
    |   2 |
    |   3 |
    |   4 |
    |   5 |
    |  99 |  -- 刚刚插入的 99
    | 100 |
    | 101 |
    +-----+

注意: insert into tablename select NULL; 等价与 insert into tablename values (NULL);

mysql>  create table test_auto_increment(a int auto_increment primary key);
    
    mysql> insert into test_auto_increment values(-1);
    
    mysql> select * from test_auto_increment;
    +----+
    | a  |
    +----+
    | -1 |
    +----+
    
    mysql> insert into test_auto_increment values(-2);
    
    mysql> select * from test_auto_increment;
    +----+
    | a  |
    +----+
    | -2 |
    | -1 |
    +----+
    
    mysql> insert into test_auto_increment values(3);
    
    mysql> select * from test_auto_increment;
    +----+
    | a  |
    +----+
    | -2 |
    | -1 |
    |  3 |
    +----+
    
    mysql> insert into test_auto_increment values(0);
    
    mysql> select * from test_auto_increment;
    +----+
    | a  |
    +----+
    | -2 |
    | -1 |
    |  3 |
    |  4 |
    +----+

mysql> select last_insert_id() --上一次自增的值

*  总结：对于auto\_increment，插入0和NULL表示自增。插入正数/负数会归到它应该去的位置。如果想要插入0，必须update … set来得到

#### 实数 ####

因为需要额外的空间和计算开销，所以应该尽量避免只在小数进行精确计算时才使用decimal—比如存储财务数据

<table> 
 <thead> 
  <tr> 
   <th>实数类型</th> 
   <th>存储空间</th> 
   <th>范围（有符号)</th> 
   <th>范围（无符号）</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>FLOAT</td> 
   <td>4 字节</td> 
   <td>(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)</td> 
   <td>0，(1.175 494 351 E-38，3.402 823 466 E+38)</td> 
   <td>单精度浮点数</td> 
  </tr> 
  <tr> 
   <td>DOUBLE</td> 
   <td>8 字节</td> 
   <td>(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td> 
   <td>0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td> 
   <td>双精度浮点数值，精度比float高</td> 
  </tr> 
  <tr> 
   <td>DECIMAL</td> 
   <td>对DECIMAL(M,D) ，如果M&gt;D，为M+2否则为D+2</td> 
   <td>依赖于M和D的值</td> 
   <td>依赖于M和D的值</td> 
   <td>小数值[精度十分高]</td> 
  </tr> 
 </tbody> 
</table>

哎，不纠结了，如果要使用小数的话，直接用DECIMAL就好

### 存储字符串 ###

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
   <th>N的含义</th> 
   <th>是否有字符集</th> 
   <th>最大长度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CHAR(N)</td> 
   <td>定长字符</td> 
   <td>字符</td> 
   <td>是</td> 
   <td>255</td> 
  </tr> 
  <tr> 
   <td>VARCHAR(N)</td> 
   <td>变长字符</td> 
   <td>字符</td> 
   <td>是</td> 
   <td>16384</td> 
  </tr> 
  <tr> 
   <td>BINARY(N)</td> 
   <td>定长二进制字节</td> 
   <td>字节</td> 
   <td>否</td> 
   <td>255</td> 
  </tr> 
  <tr> 
   <td>VARBINARY(N)</td> 
   <td>变长二进制字节</td> 
   <td>字节</td> 
   <td>否</td> 
   <td>16384</td> 
  </tr> 
  <tr> 
   <td>TINYBLOB(N)</td> 
   <td>二进制大对象</td> 
   <td>字节</td> 
   <td>否</td> 
   <td>256</td> 
  </tr> 
  <tr> 
   <td>BLOB(N)</td> 
   <td>二进制大对象</td> 
   <td>字节</td> 
   <td>否</td> 
   <td>16K</td> 
  </tr> 
  <tr> 
   <td>MEDIUMBLOB(N)</td> 
   <td>二进制大对象</td> 
   <td>字节</td> 
   <td>否</td> 
   <td>16M</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB(N)</td> 
   <td>二进制大对象</td> 
   <td>字节</td> 
   <td>否</td> 
   <td>4G</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT(N)</td> 
   <td>大对象</td> 
   <td>字节</td> 
   <td>是</td> 
   <td>256</td> 
  </tr> 
  <tr> 
   <td>TEXT(N)</td> 
   <td>大对象</td> 
   <td>字节</td> 
   <td>是</td> 
   <td>16K</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT(N)</td> 
   <td>大对象</td> 
   <td>字节</td> 
   <td>是</td> 
   <td>16M</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT(N)</td> 
   <td>大对象</td> 
   <td>字节</td> 
   <td>是</td> 
   <td>4G</td> 
  </tr> 
 </tbody> 
</table>

#### varchar(n) 与char(n) ####

*  varchar：  
    1、存储可变长字符串，比定长类型更加节省空间，因为它仅仅使用必要的空间  
    2、varchar需要使用1或者2个额外字节记录字符串的长度：如果列的最大长度小于或者等于255字节，则只需要使用1个字节表示，否则需要两个字节  
    3、什么时候使用varchar
    
     *  字符串列的最大长度比平均长度长很多
     *  列的更新很少：不容易产生碎片
     *  使用了像UTF-8这一复杂的字符集，每个字符使用不同的字节数进行存储
     *  用的最多，可以解决大部分场景
 *  char  
    1、存储定长字符串：在存储时会删除所有的末尾空格，但是如果在比较时存储的字符串少于定义的长度，会用空格填充以便方便比较  
    2、什么时候使用char
    
     *  存储很短的字符串，或者所有值都接近一个长度：比如存储密码的MD5值
     *  经常变更的数据：不容易产生碎片
     *  非常短的列

#### binary(n) 和 varbinary(n) ####

*  varbinary：  
    类似varchar，但是它存储的是二进制字符串，存储的是字节码而不是字符
 *  binary  
    类似char，但是填充时使用\\0填充而不是空格

mysql> select length('我'),char_length('我');
    +---------------+--------------------+
    | length('我')  | char_length('我')  |
    +---------------+--------------------+
    |             3 |                  1 |
    +---------------+--------------------+

补充：字符串基本上不区分大小写，除非

mysql>create table t(a varchar(10) collate utf8mb4_bin, unique key(a));

不过这个场景用的非常少

#### BLOB与TEXT类型 ####

尽量少用这两种类型  
1、BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储  
2、它们分别属于两组不同的数据类型家族：字符类型是TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT和LONGTEXT；二进制家族是：TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB和LONGBLOB。  
3、BLOB家族和TEXT家族仅有的不同是：blob存储二进制数据，没有排序规则或字符集，而text有字符集和排序规则  
4、MYSQL对blob和text列进行排序与其他类型是不同的：它只对每个列的最前max\_sort\_length字节而不是整个字符串排序。  
注意：

*  在BLOB和TEXT上创建索引时，必须指定索引前缀的长度

mysql> create table test_text(a int primary key, b text, key(b));
    ERROR 1170 (42000): BLOB/TEXT column 'b' used in key specification without a key length
    
    mysql> create table test_text(a int primary key, b text, key(b(64)));

*  BLOB和TEXT列不能有默认值

mysql> create table test_text(a int primary key, b text default 'aaa', key(b(64)));
    ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'b' can't have a default value

*  BLOB和TEXT列排序时只使用该列的前max\_sort\_length个字节

mysql> select @@max_sort_length;
    +-------------------+
    | @@max_sort_length |
    +-------------------+
    |              1024 |
    +-------------------+

不建议在MySQL中存储大型的二进制数据，比如歌曲，视频

#### 字符集 ####

*  字符集  
    一组符号和编码的集合叫做字符集。常见的有：utf8、utf8mb4【推荐】、gbk、gb18030

mysql>  SHOW CREATE DATABASE db_name;  -- 查看当前数据库编码：
    
    mysql>　SHOW CREATE TABLE tbl_name;   --查看表编码：
    
    mysql> SHOW FULL COLUMNS FROM tbl_name;  -- 查看字段编码：

mysql> alter table table_name convert to character set utf8mb4  -- 修改表的字符集

*  collation：指字符串的排序规则，ci（case insensitive）结尾的排序集是大小写不敏感的，默认是不区分大小写的

mysql> select 'a' = 'A';   --select 'a' = 'A    ';
    +-----------+
    | 'a' = 'A' |
    +-----------+
    |         1 |  -- 因为大小写无关，所以返回1
    +-----------+
    
    mysql> create table test_ci (a varchar(10), key(a));
    
    mysql> insert into test_ci values('a');
    mysql> insert into test_ci values('A');
    
    mysql> select * from test_ci where a = 'a';
    +------+
    | a    |
    +------+
    | a    |
    | A    |  -- A也被我们查到了
    +------+

用户名不希望区分大小写

*  修改默认collation

mysql> set names utf8mb4 collate utf8mb4_bin;  -- 当前会话有效
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select 'a' = 'A';
    +-----------+
    | 'a' = 'A' |
    +-----------+
    |         0 |
    +-----------+
    1 row in set (0.00 sec)

字符集的指定，可以在创建数据库的时候指定，也可以在创建表的时候单独指定，也可以创建列的时候进行指定

### 集合类型ENUM 和 SET ###

*  ENUM类型最多允许65536个值
 *  SET类型最多允许64个值
 *  通过sql\_mode参数可以用户约束检查

mysql> create table test_col (
        -> user varchar(10),
        -> sex enum('male', 'female')  -- 虽然写的是字符串，单其实存储的整型，效率还是可以的
        -> );
    
    mysql> insert into test_col values ("tom", "male");
    Query OK, 1 row affected (0.02 sec)
    
    mysql> insert into test_col values ("tom", "xmale");  -- 不是male 和 female
    Query OK, 1 row affected, 1 warning (0.03 sec)  -- 有warning
    
    mysql> show warnings;
    +---------+------+------------------------------------------+
    | Level   | Code | Message                                  |
    +---------+------+------------------------------------------+
    | Warning | 1265 | Data truncated for column 'sex' at row 1 |
    +---------+------+------------------------------------------+
    
    mysql> show variables like 'sql_mode';
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | Variable_name | Value                                                                                                                                     |
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    
    mysql> set sql_mode='strict_trans_tables';  -- 设置为严格模式
    Query OK, 0 rows affected, 2 warnings (0.00 sec)
    
    mysql> show warnings\G
    *************************** 1. row ***************************
      Level: Warning
       Code: 3135
    Message: 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
    *************************** 2. row ***************************
      Level: Warning
       Code: 3090
    Message: Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
    2 rows in set (0.00 sec)
    
    mysql> show variables like 'sql_mode';
    +---------------+---------------------+
    | Variable_name | Value               |
    +---------------+---------------------+
    | sql_mode      | STRICT_TRANS_TABLES |
    +---------------+---------------------+
    
    mysql> insert into test_col values ("tom", "xmale");
    ERROR 1265 (01000): Data truncated for column 'sex' at row 1

*  强烈建议新业务上都设置成严格模式
 *  enum和set适合存储固定信息，比如人的性别，产品状态
 *  enum列允许在列中存储一组定义值中的单个列，set列允许在列中存储一组定义值中的一个或者多个值

### BIT ###

慎用

参考：<高性能Mysql>