MySQL 基本数据类型选择要合适

背景介绍：在实际项目中，我发现很多人对数据类型选择不当，比如：本来只是表示简单的五六种状态用的类型是int(实际上tinyint就可以满足要求)、字符串vacharh和char的区别搞不清楚、如果不可能为负数时又没有设置为unsigned、以及int(1)和int(11)实际上占用空间是一样的等等。

选择的原则是：在满足使用的前提，要小而简单。

（1）整形比字符操作代价更低。

（2）保存日期格式时，要选用MySQL内置的日志格式（TimeStamp, DateTime）,而不使用字符串。PS:TimeStamp使用4个字节进行存储（存储的时间从1970到2034），而DateTime使用8个字节存储（时间从1001到9999）,上述两种时间格式推荐使用TimeStamp. 上述的两种日期格式的精度都只是到秒，如果要存储到毫秒或者更高的精度，可以考虑用BIGINT来保存。

（3）小数点的保存如何有涉及到运算，用float和double可能会精度不准，可以考虑将小数乘于相应的倍数，然后用BIGINT来存储。

（4）整数的几种类型

TINYINT、SMALLINT、MEDIUMINT、BIGINT分别使用8，16,32,64位。保存范围为-2^N到2^N-1（这里N=位数-1），如果加入UNSIGNED则保存的范围数（在正数范围内）提升一倍。

注意：对于INT(1)和INT(20)来说存储的空间和计算是一样的，因此指定位数是没有意义的。

（5）关于存储字符串的几种类型的选择

主要有CHAR, VARCHAR, BLOB, TEXT

关于CHAR和VARCHAR

其中CHAR（10）是定长为10（只需要10个字节）的，而VARCHAR(10)是变长的（不仅需要10个字节，还需要记录长度，如果长度小于255则额外需要1个字节，如果大于255则需要两个字节）。

在使用的时候，如果是定长字符推荐CHAR（N），比如定长的MD5加密的字符串。

CHAR的优势是经常更新不会产生太多的碎片。

对于VARCHAR中的变长个数如果定义的太长也会浪费存储空间的。比如VARCHAR(5)和VARCHAR(100)存储“hello”存储的空间都是一样的，但是更长的varchar的列会消耗更多的内存。

关于BLOB和TEXT

两个都是存储长的字符串，而BLOB存储的格式是二进制，TEXT是字符串。

上述的两种类型都有TINYBLOB，SMALLBLOB,BOLOB,MEDIUMBLOB,LONGBLOB,BLOB

以及TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT

（6）关于保存ip地址

对于通常的ipv4，比如：192.168.1.123，我们会用15位char来存储这种是不推荐的，实际上ipv4是32位的无符号整数，推荐将ipv4转成整数进行保存。

在MySQL中提供了INET_ATON()将ipv4转成整形以及INET_NTOA()将整形转成ipv4

一个示例的用法如下：

create table tbl_ip(ip int unsigned, name char(1))

insert into tbl_ip(inet_aton(‘192.168.1.122’),’d’);

select * from tbl_ip where ip=inet_aton(‘192.168.1.122’);

select inet_ntoa(ip) from tbl_ip;

（7）默认值的缺省值的设置

尽量不用NULL，可以设置为“”或者0。

（8）在正式环境如何更改表的结构

方法一：创建影子表，当完成后，通过一个原子的重命名操作切换影子表和原表

MySQL语句的例子：

DROP IF EXISTS tbl_channel_new, tbl_channel_old;

CREATE TABLE tbl_channel_new LIKE tbl_channel;（注意：这里只是复制了表的结果，其数据集仍然是空的）

…(执行修改操作)

RENAME TABLE tbl_channel TO tbl_channel_old, tbl_channel_new TO tbl_channel;

最后新表就是我们修改的表，并保存了一份之前的表，如果新表有问题可以快速回滚。

方法二：在更改表的时候可能会遇到表很大，而内存又不足，以及索引又很多，此时如果执行ALTER TABLE操作可能需要花费数个小时甚至数天的时间才能完成。

通过.frm文件的替换完成，但是实际实验中不成功。

方法三：通常表的修改应该在备机中执行ALTER并在完成后把它切换成主库。

（9）MySQL中的计数器

使用的用法：

UPDATE tbl_stat SET cnt=cnt+1;

上面的操作在计数的时候可以不用取得当前的值，而直接通过指定的列和要增加的值来计算统计结果。

在数据统计中的问题，对于某一行的计数由于会有事务，因此都会有一个全局的互斥锁，使得并发性能不高。

改进方法：

将计算器保存在多行中，每次随机选择一行进行更新，示例代码如下：

CREATE TABLE hit_counter(

slot tinyint unsigned not null primary key,

cnt int unsigned not null

)ENGINE=InnoDB;

然后预先在这张表上增加100行数据。随机选择一个槽（slot）进行更新：

UPDATE hit_counter SET cnt=cnt+1 WHERE slot=RAND()*100；

进一步改进，如果需要每隔一段时间开始一个新的计数器（比如：每天一个），则修改表为：

CREATE TABLE daily_hit_counter(

day date not null,

slot tinyint unsigned not null,

cnt int unsigned not null,

primary key(date, slot)

)ENGINE=InnoDB;

在这个场景中，可以不用预先增加行，而用ON DUPLICATE KEY UPDATE代替（即如果不存在指定的主键KEY则插入，存在则执行更新操作），语句如下：

INSERT INTO daily_hit_counter(day, slot, cnt) VALUES(CURRENT_DAY, RAND()*100, 1) ON DUPLICATE KEY UPDATE cnt=cnt+1；