数据类型
整型
1.tinyint 1Bytes -128~127(255)
2.smallint 2Bytes -32768~32676(65535) 3.mdeiumint 3Bytes -8388608~8388607(16777215) 4.int 4Bytes -2147483648~2147483647(4294967295,42亿) 5.bigint 8Bytes -9223372036854775808~9223372036854775807(18446744073709551615) int列如果是主键不能online DDL 转成bigint.- onlineDDL :pt-osc(更容易导致主从数据延时,执行过程报错,无法从上一个位置开始,只能重头开始,pt 创建trigger 或者删除trigger 的时候 有坑。 )和gh-ost(推荐,因基于binlog,可以随时停止或继续).推荐使用gh-st
int(11),11是修饰符,不是长度限制,int(8)zerofill zerofill也是修饰符,左侧补零.
溢出: cast(9223372036854775807 as unsigned) 改为不符号IPv4地址可以用INT存储:
select length('255.255.255.255') +---------------------------+ | length('255.255.255.255') | +---------------------------+ | 15 | +---------------------------+ root@localhost [(none)]>select inet_aton('255.255.255.255'); //IPv4最大值,正好是int的无符号数最大值. +------------------------------+ | inet_aton('255.255.255.255') | +------------------------------+ | 4294967295 | +------------------------------+ root@localhost [(none)]>select inet_ntoa(4294967295); +-----------------------+ | inet_ntoa(4294967295) | +-----------------------+ | 255.255.255.255 | +-----------------------+IPv6和IPv4共用的方法(数据类型为VARBINARY(16)，而不是BINARY(16)。唯一的原因是MySQL函数同时适用于IPv6和IPv4地址。 BINARY(16)只适用于存储IPv6地址，并保存一个字节。在处理IPv6和IPv4地址时应使用VARBINARY(16)):
root@localhost [(none)]>SELECT HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
+----------------------------------------------+ | HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089')) | +----------------------------------------------+ | FDFE0000000000005A55CAFFFEFA9089 | +----------------------------------------------+ 1 row in set (0.00 sec) root@localhost [(none)]>SELECT HEX(INET6_ATON('192.168.9.1')); +--------------------------------+ | HEX(INET6_ATON('192.168.9.1')) | +--------------------------------+ | C0A80901 | +--------------------------------+ 1 row in set (0.00 sec)mysql> SELECT INET6_NTOA(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
-> 'fdfe::5a55:caff:fefa:9089' mysql> SELECT INET6_NTOA(INET6_ATON('192.168.9.1')); -> '192.168.9.1'mysql> SELECT INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089'));
-> 'fdfe::5a55:caff:fefa:9089' mysql> SELECT INET6_NTOA(UNHEX('C0A80901')); -> '192.168.9.1'- 时间戳也是推荐使用无符号int也存储. select unix_timestamp(); root@localhost [(none)]>select unix_timestamp(); +------------------+ | unix_timestamp() | +------------------+ | 1539644023 | +------------------+ 1 row in set (0.00 sec) root@localhost [(none)]>select from_unixtime(1539644023); +---------------------------+ | from_unixtime(1539644023) | +---------------------------+ | 2018-10-16 06:53:43 | +---------------------------+ 1 row in set (0.00 sec)
时间
优先使用:timestamp,其次datetime
timetamp\datetime 从5.6.6开始均支持自动更新为current_timestamp 日期转换: CAST()datetime_col as DATE) SELECT NOW()+0;root@localhost [wenyz]>SELECT NOW()+0;
+----------------+ | NOW()+0 | +----------------+ | 20181016075647 | +----------------+ 1 row in set (0.00 sec)root@localhost [wenyz]>SELECT NOW();
+---------------------+ | NOW() | +---------------------+ | 2018-10-16 07:56:54 | +---------------------+ 1 row in set (0.00 sec)root@localhost [wenyz]>select CAST(now() as DATE)
-> ; +---------------------+ | CAST(now() as DATE) | +---------------------+ | 2018-10-16 | +---------------------+ 1 row in set (0.00 sec)root@localhost [wenyz]>select CAST(now() as DATE);
+---------------------+ | CAST(now() as DATE) | +---------------------+ | 2018-10-16 | +---------------------+ 1 row in set (0.00 sec)root@localhost [wenyz]>select CAST(now() as time);
+---------------------+ | CAST(now() as time) | +---------------------+ | 07:57:53 | +---------------------+ 1 row in set (0.00 sec)root@localhost [wenyz]>select now(),now()+5;
+---------------------+----------------+ | now() | now()+5 | +---------------------+----------------+ | 2018-10-16 07:59:36 | 20181016075941 | +---------------------+----------------+ 1 row in set (0.00 sec)字符
1.char(M) Mw(字符集的单字符字节数) bytes,0<=M<=255 (utf8 2553,utf8.mb4:2554) 2.BINARY(M) M bytes,0<=M<=255 3.VARCHAR(M),VARBINARY(M) L+1 bytes if column values require 0-255 bytes if values may require more than 255 bytes,L+2bytes if values may require more than 255 bytes.(除Mw都需要额外1或2个节点存信息),实际存储长度超过255字节时,会被做TEXT处理.所有VARCHAR列的总可用长度是65535字节(实际上是65533)
字符集是utf8mb4时,实际可存储字符数是FLOOR(65533/4)=16384 4.mysql8.0默认字符集是utf8mb4长文本\大对象
| data type | storange required |
|---|---|
| tinyblob,tinytext | L+1bytes,where L<2^8 |
| Blob,text | L+1bytes,where L<2^16 |
| mediumblob,mediumtext | L+4bytes,where L<2^24 |
| longlob,longtext | L+4bytes,where L<2^32 |
- 超长字段有可能会发生行溢出Off page (overflow)存储.一个page为16k,当一行超过8k时就会发现行溢出.
- 尽量不能,无法避免时则尽量独立表存放
- 对text列排序时,实际排序长是max_sort_length字节(默认1kb,排序时最多只考虑字段的前1kb)
实例:一个100G的表拆分成4个表后,总大小仅25G
浮点\小数位
FLOAT是单精度- 用于存储一般精确度金额
- 事实上,更建议把金额转换成INT来存储,更精确.
- FLOAT列类不指定精度时不能等值查询
- num FLOAT,where num=0.12,查询结果为空,需要用范围查询.
- num FLOAT(9,7),where num=0.12,可以查询到. DECIMAL
- 精确的十进制浮点型,高精度计算
- M最大65(默认10),D最大30(默认0)
小数点后面的位数超限后,自动四舍五入(SQL_MODE=''时)
枚举,ENUM
ENMU(VALUE_LIST)
- Value_list数量不超过255时,占用1个字节,超过时占2字节,最多65535个
- 表面上是用字符型表示,但实际底层采用INT来存储,显示时再转换为之前存储表示的值
- 所以,可以直接用TINYINT/SMALLINT代替(在单独的字典表里对应1表示xxx,2表示bbb,在需要枚举的列直接用数字来表示就可以了),
- 使用注意:千万不要用char或vchar来存储枚举.
类型选择建议:
- 最大限度减少IO请求
- 让数据表每行长度越小越好,可以避免行迁移或碎片
- 有TEXT/BLOB等大列时,尽量拆分到独立子表中,
或者使用TokuDB引擎 - 经常更新的列和不经常更新的列分开
不同的表存储. - 更新数据也尽可能不要让长度变大.