慢慢的成长之路
0%

MySql数据类型说明和建议

Posted on Edited on In

之前一直用的是 Oracle，现在用 MySql 了，就看下 MySql有哪些数据类型，该怎么使用呢？其实最后发现变化也不是很大，上手也很快。

MySql的数据类型

整型

数据类型 含义
tinyint(m) 1个字节  范围(-128~127)
smallint(m) 2个字节  范围(-32768~32767)
mediumint(m) 3个字节  范围(-8388608~8388607)
int(m) 4个字节  范围(-2147483648~2147483647)
bigint(m) 8个字节  范围(+-9.22*10的18次方)

取值范围如果加了unsigned，则最大值翻倍，例如：

tinyint unsigned 的取值范围为(0~256)。

浮点型

数据类型 含义
float(m,d) 单精度浮点型    8位精度(4字节)
double(m,d) 双精度浮点型    16位精度(8字节)

m表示总数位，d表示小数位

例如一个字段定义为float(5,3), 插入一个数123.45678, 实际数据库里存的是123.457，因为d = 3，但总个数还以实际为准，即6位。

定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

decimal(m,d) 参数 m < 65 是总个数，d<30 且 d<m 是小数位。

字符串

数据类型 含义
char(n) 固定长度，最多255个字符
varchar(n) 可变长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符

char 和 varchar

  • char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。

  • char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4)，存入3个字符将占用4个字节。

  • char类型的字符串检索速度要比varchar类型的快。

varchar 和 text

  • varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。

  • text类型不能有默认值。

  • varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text，在都创建索引的情况下，text的索引似乎不起作用。

二进制数据

  • BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写。

  • BLOB存储的数据只能整体读出。

  • TEXT可以指定字符集，BLOB不用指定字符集。

日期时间类型

数据类型 含义
date 日期 ‘2008-12-2’
time 时间 ‘12:25:36’
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 自动存储记录修改时间

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

数据类型的属性

关键字 含义
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号
CHARACTER SET name 指定一个字符集

最佳实践

情况 建议
不定长的字符串 varchar
类似枚举，比如’S’,’F’ char(1)
时间 datetime
update_time timestamp
金额 bigint
各种id bigint

  1. 枚举一般不要使用0, 1, 2 之类的，很难了解其意思，不如直接用’S’, ‘F’ 或者多个固定长度字符 ‘POS’, ‘NET’等

  2. 不要用小数存放货币，货币金额化成分为单位存放在库中，例如：100.00RMB，存放在数据库中就是：10000

  3. 一些 id 可以用 bigint，比如 cust_id等

  4. 自增字段需要添加 AUTO_INCREMENT 来标识

  5. 一般的日期建议使用datatime， 特别的记录数据更新时间的 update_time 字段使用 timestamp 来标识，记得添加：ON UPDATE CURRENT_TIMESTAMP

  6. 有时日期可以拆开，年月日和时分秒分开存放，例如：’2017-12-05 09:17:11’ 分别存放为：
    char(8)类型 的 ‘20171205’ 和 char(6)类型的 ‘091711’，这样可以根据日期做分区，查询时有时也很简单，直接根据日期来匹配就行，例如：要筛选日期在 2017-12-01 至 2017-12-03 的数据，直接用 time >= ‘20171201’ and time <= ‘20171203’就好，但如果使用的是 datetime，则 time >= ‘2017-12-01’ and time < ‘2017-12-04’ 如果直接用 time >= ‘2017-12-01’ and time <= ‘2017-12-03’，则不会包含2017-12-03的数据

  7. 必须把字段定义为NOT NULL，并且提供默认值

    • null 的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
    • null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
    • null 值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识
    • 对 null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。

      如：where name != ‘orca’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

  8. 禁止使用TEXT，BLOB类型

  9. 禁止使用ENUM，可使用TINYINT代替

  10. 存储手机号使用varchar(20)

  11. 小数类型为 decimal，禁止使用 float 和 double，会损失精度