MySQL数据类型介绍

1. 越小越好，越简单越好

一般情况下，应该尽量使用可以正确存储数据的最小最简单的数据类型。因为它们占用更小的磁盘，内存和CPU缓存，处理时需要的CPU周期也更少。但同时，在确定选择时也务必注意不要低估需要存储的值的范围，否则会给后期带来麻烦。

2. 尽量避免NULL

NULL是列的默认属性，但通常情况下最好指定列为NOT NULL，除非真的需要存储NULL。因为含有NULL的列使得索引、索引统计和值比较都更加复杂，而且当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在myisam表里甚至还可能导致固定大小的索引变成可变大小的索引。

将可为null的列改为not null带来的性能提升比较小，所谓除非必要，否则不必特意去修改，但，如果计划在列上建索引，就应该尽量避免涉及成可为null的列。

InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据（很多值为NULL，只有少数行的列有非NULL值）有很好的空间效率，但这一点不适用于myisam。

3. IP的存储

IP并不是字符串，而是32位无符号整数，用小数点将地址分为四段的表示方法只是为了让人们阅读容易，所以应该用无符号整数存储IP地址，MySQL提供了INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换

mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `a` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> insert into test values(inet_aton(‘192.168.1.200‘)), (inet_aton(‘200.100.30.241‘));
Query OK, 2 rows affected (0.01 sec)
 
mysql> select * from test;
+------------+
| a          |
+------------+
| 3232235976 |
| 3362004721 |
+------------+
 
mysql> select inet_ntoa(a) from test;
+----------------+
| inet_ntoa(a)   |
+----------------+
| 192.168.1.200  |
| 200.100.30.241 |
+----------------+

二 具体数据类型介绍

1. 数字类型

（1）整数类型   

基本使用：整数类型（显示宽度）

注：显示宽度对于数值的大小并无影响，只是当设置了zerofill的时候，在显示的时候补0而已。建议不要设显示宽度及zerofill

（2） 浮点数类型

基本使用：数据类型（M,D）

注1：M是指精度，总的数字长度；D是指标度，小数点后的数字长度，如DECIMAL(5,2) 可以存储5个数字和两个小数，即存储范围为-999.99到999.99。

注2：分为三类，DECIMAL（定点数）、FLOAT（单精度浮点型）、DOUBLE（双精度浮点型），一般用DECIMAL来存储精确数据，如工资等，但除非必须，否则最好不要用浮点数类型，可以考虑使用bigint代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

（3）超出范围和溢出处理

当MySQL存储了一个超出了指定范围的数值，其结果依赖于SQL mode的设置。

• 如果sql mode为严格模式，MySQL会报错，数据插入失败
• 如果为非限制模式，MySQL会将数值转化为范围允许内的最大或最小值进行存储，如数据类型为TINYINT 或TINYINT UNSIGNED 时，存储数值256进去后会被转化为127或255。

2. 时间类型

 （1）date

范围：1000-01-01 to 9999-12-31

（2）datetime(fsp)    fsp代表小数点后的位数

范围：1000-01-01 00:00:00.000000  to  9999-12-31 23:59:59.999999  

eg:  CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

（3）timestamp(fsp)    fsp代表小数点后的位数

范围：1970-01-01 00:00:01.000000  UTC to  2038-01-19 03:14:07. 999999  UTC

特性：时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区

定义时加上DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 会设定当前时间为默认值并在后续更新该行中的任意值时更新时间为当前时间。

定义时只加上DEFAULT 而不加 ON UPDATE CURRENT_TIMESTAMP 会设定当前时间为默认值，后续更新该行内容时不再更新该时间值。DEFAULT有两种选择，一种是DEFAULT CURRENT_TIMESTAMP ，一种是可以具体指定一个时间。


CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE t1 (
ts TIMESTAMP DEFAULT 0,
dt DATETIME DEFAULT 0
);

（4）time

范围：-838:59:59.000000   to  838:59:59.000000  

（5）year

范围：year(4) : 1901 to 2155  

          year(1/2):1 to 99   1-69会被转换为2001 to 2069 ，70-99会被转换为1970 to 1999   MySQL5.7.5之后已被去掉  

3. 字符类型

（1） char和varchar

• 它们的存储、数据恢复、最大长度和后面的空格是否保留等方面有所不同
• char(n)和varchar(m)中的n/m代表的是字符，如char(30)就可以存储30个字符，具体占用多少字节与字符集有关
• char是定长字符，char(n)，n的范围是0-255，其值被存储时，值的右边会被填充指定长度的空格。当值被取回时，除非PAD_CHAR_TO_FULL_LENGTH SQL mode 被指定，否则末尾的空格会被移除
  
• varchar被存储时并不会被填充空格，如果末尾有空格的话，在存储和取回数据时都会被按原样保持，与标准sql一致
• varchar是可变长度的字符，varchar(m)，m的范围是0-65535，其值的最大有效长度为最大行长度65535 bytes，如果值不大于255 bytes，该列会占用1个字节来存储该值的长度（单位为bytes），如果值大于255bytes，该列会占用2字节来存储该值长度。
• 对于char和varchar，如果不使用严格sql mode，当插入超出限定范围的值后，该值会被切断并生成一个警告。如果设定为严格模式的话，一旦插入值的非空格字符会被切断就会会被阻止并诱发一个报错。
• varchar比定长类型更节省空间，因为它仅使用必要的空间，但，由于行是变长的，在update时可能使行变得比原来更长，这就导致需要做额外的工作，如果当前页内没有更多的空间存储，innodb还需要分裂页来使行可以放进页内，所以需要视使用情况来决定，当字符串列的最大长度比平均长度大很多，列的更新很少，或使用了像utf8这样的字符集，每个字符都使用不同的字节数进行存储等情况下可以采用varchar。

（2）binary和varbinary

• 它们与char和varchar类似，唯一不同的就是它们存储的是字节码，这意味着它们没有字符集，排序比较时是基于值中比特数的值。
• 允许的最大长度也类似，只是这个长度是比特长度，而不是字符长度。
• 对于binary，插入时值的末尾会由0x00 (the zero byte) 来填充，查取数据时末尾的填充不会被移除，在数据比较时，包括order by和distinct操作等，所有的bytes都是显式的，都参与比较，另外，0x00 和空格在比较时是不同的，0x00 < 空格。
  
• 对于varbinary，插入时值的末尾不会被填充，查取数据时也不会被截取数据。在比较时，所有的bytes都是显式的，同binary一样。
• 如果数据在查取时必须与原存入时的数据完全一致的话，用varbinary或blob中的一种会更好，用binary的话其填补功能会造成一定变化，如下所示：

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)

（3）blob和text

• blob分为TINYBLOB, BLOB, MEDIUMBLOB 和 LONGBLOB ，text分为TINYTEXT, TEXT, MEDIUMTEXT 和  LONGTEXT
  
• 在blob和text列上加索引时必须加前缀所有
• blob和text列上不能有default值
• 在排序时，只会用列值的前max_sort_length 比特的数据进行比较，max_sort_length的默认值为1024，可以在线更改。
• 查取text或blob时产生的中间结果会存储在硬盘上的一个临时表中，而不是内存中的临时表，因为内存中的临时表用的是memory引擎，它不支持text和blob数据类型，所以除非必要，一般不要查取text和blob列，例如要避免使用select *
• 其可存储的数据长度决定于可用的内存和buffer的大小，基本可以随便存，当其太大时，innodb会分配专门的外部区域来进行存储，此时每个值在行内仅需要1-4个字节来存储一个指针，然后在外部存储区域存储实际的值

 （4）enum和set

• enum是枚举类型，枚举列可以把一些不重复的字符串存储成一个预定的集合。MySQL在存储枚举时会根据列表值的数量压缩到一个或两个字节中，在内部将每个值在列表中的位置保存成整数，并在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。所以要避免用数字作为enum枚举常量，很容易引起混乱。

  create table test(e enum(‘fish‘, ‘apple‘, ‘dog‘) not null);
  
  insert into test values(‘fish‘),(‘dogs‘),(‘apple‘);
  
  mysql> select e+0 from test;
  +-----+
  | e+0 |
  +-----+
  |   1 |
  |   3 |
  |   2 |
  +-----+

• enum排序时是按照内部存储的整数而不是定义的字符串进行

  mysql> select e from test;
  +-------+
  | e     |
  +-------+
  | fish  |
  | dog   |
  | apple |
  +-------+　　

• enum的缺点是字符串列表是固定的，需要添加或删除字符串时必须使用alter table
• enum只能单选，但set可以多选，如enum的值是‘A,B,C’时，可选的要么是A，要么是B，要么是C，如果是set，就可以是“A,B”，“A,B,C”。

MySQL数据类型介绍

标签：32位   效率   row   否则   最小   指针   div   取数   数字