Mysql学习笔记三， 数据类型的选择

Char和VarChar

如图所示:
1 最后一列是指在非严格模式， 严格模式下会直接报错。
2 即使插入同样的数据，也可能返回不一样的数据:
create table vc(id1 char(4),id2 varchar(4));
insert into vc values('1 ','1 ');
select concat(id1,'|'), concat(id2,'|') from vc;
分别返回了1| 和2 |
3 char处理效率高于varchar, 但varchar更节省空间，随着mysql版本的上升，效率上
的差距越来越小 。

在各个存储引擎下使用原则：
1 MyIsam: 使用char。
2 Innodb: 使用varchar。引擎内部没有区分定长和非定长，所以使用char并不会带来
效率提升。
3 Memory: 即使使用varchar，也会被当做char使用。

Text和Blob

存放大量数据的时候需要考虑使用text和blob, text存放字符型数据，blob存放二进制数据。
Text有Text、MediumText、LongText,Blob类似。

1 这些大字段，执行了删除操作后，会留一下空洞， 后面插入这些空洞的记录插入性能
比较差，建议在删除操作后进行optimize table。

create table t1(id int,val text); --建表,val为大字段
insert into t1 values(1, repeat('haha',2000));--使用repeat来构建大字段
insert into t1 values(2, repeat('haha',2000));
insert into t1 select * from t1;--执行N次，以指数式增长方式来添加数据
/databasehome/du -sh t1.*  --查看t1.ibd文件的大小(表数据和索引的大小)
delete from t1 where id =1; --清空一半的数据
/databasehome/du -sh t1.*  --此时文件大小未变。
optimize table t1;  --优化表t1
/databasehome/du -sh t1.*  --此时空间被回收，文件大小变小。

2 通过合成索引(字段的散列指作为索引)来提高对大字段的检索,只适用精确检索。

create table t (id varchar(100),context blob,hash_value varchar(40));
insert into t values(1,repeat('beijing',2),md5(context)); --hash_value存放context被hash后的数据， 用于精确检索时使用

3 模糊检索使用前缀索引。

create index t1_idx2 on t1(val(10)); --将val的前10个字符作为前缀索引
desc select * from t1 where val like 't%'; --通过查看查询语句的执行情况，发
现是用来了前缀索引。

• %放在前面无法使用前缀索引。
  4 尽量避免检索大字段，如少使用select *。将大字段做一个单独表存放。

浮点数和定点数

浮点数： float,double(real) 含有一部分小数位的数值， 如果超过其精度，数据可以插入， 但是不会被四舍五入。是一种近似值的方式。
定点数: decimal(numberic) ，以字符串方式存放，相对浮点数精度更加准确。 适合用于计算。 如果超过精度，在默认SqlMode下会告警并插入数据， 如果在传统模式下直接报错。

create table t1(a float(10,2), b decimal(10,2));
insert into t1 values(131072.32,131072.32); --插入131072.32 ,浮动数的情况下，查询出来的是131072.31

• 编程中，比较和运算。尽量使用定点数。

日期类型的选择

1. 按需选择。如果只需要记录年份，能使用YEAR即可。
2. 如果要记录年月日时秒分， 并且记录时间较久远，可以使用DATETIME， 因为它比TIMESTAMP时间跨度更长。
3. 如果记录要在不同时区使用， 可以使用TIMESTAMP， 它和实际时区相对应。