MySQL中常用的数据类型

整型

• int

  • 有符号
  • 范围: -2147483648 ~ 2147483647

• int unsigned

  • 无符号
  • 范围: 0 ~ 4294967295

• int(5) zerofill

  • 仅用于显示，当不满足5位时，按照左边补0，例如: 00002
  • 满足时，正常显示

• tinyint[(m)] [unsigned] [zerofill]

  • 有符号，取值范围 -128 ~ 127
  • 无符号，取值范围 0 ~ 255

• bigint[(m)][unsigned][zerofill]

  • 有符号，取值范围: -9223372036854775808 ~ 9223372036854775807
  • 无符号，取值范围: 0 ~ 18446744073709551615

示例

• create table L1(id int, uid int unsigned, zid int(5) zerofill) default charset=utf8;
• insert into L1(id, uid, zid) values(1,2,3)
  • 这里的 zid 3 这么显示: 00003
• insert into L1(id, uid, zid) values(21444444, 4000000, 3000000)
  • 这里的 zid 3000000 显示 3000000
  • 如果 插入数值，超过类型范围，则会报错，这个需要注意， Out of range value for column
  • 可以配置不报错，但截断，不建议使用

小数

• decimal[(m[,d])] [unsigned] [zerofill]
  • 准确的小数值
  • m是数字总个数 (负号不算)， 最大值 65
  • d是小数点后个数， d最大值 30

sql 示例

create table L2(id int not null primary key auto_increment,salary decimal(8, 2)
) default charset=utf8;

• 如果插入 小数不是 2位，会自动四舍五入

• 如果整数部分超出，则会报错(严格模式)

• 项目开发中，一般都用 decimal, float, double 不常用

• FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

  • 单精度浮点数，非准确小数值
  • m是数字总个数，d是小数点后个数
  • 底层存储是 32位

• DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

  • 双精度浮点数 (非准确小数值)
  • m是数字总个数
  • d是小数点后个数
  • 底层存储是 64位

字符串

• char(m)

  • 定长字符串
  • m 代表字符串的长度，最多容纳 255 个字符
  • 定长的体现
    • 及时内容长度小于 m， 也会占用 m长度
    • 例如, char(5), 数据是 yes，底层也会占用5个字符
    • 如果超出 m 长度限制 (默认 mysql是严格模式，所以会报错)
    • 如果在配置文件中，加入如下配置
    • sql-mode="NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION"
    • 保存并重启，此时 mysql 则是非严格模式，此时超过长度则自动截断 (不报错)
  • 注意
    • 默认底层存储是固定的长度 (不够则用空格补齐)，但是查询数据时，会自动将空白去除
    • 如果想要保留空白，在 sql-mode中加入 PAD_CHAR_TO_FULL_LENGTH 即可，但是一般不加
    • 查看模式 sql-mode, 执行命令: show variables like ‘sql_mode’
    • 一般适用于: 固定长度的内容

• varchar(m)

  • 变长字符串，m代表字符串的长度，最多可容纳 65535 个字节(和编码有关，utf8的编码一个字符占用三个字节)
  • 同上，超出后会报错，配置非严格后会自动截断

例如

-- 创建 表 L3
create table L3(id int not null primary key auto_incrementname varchar(5),depart char(3)
) default charset=utf8;insert into L3(name, depart) values("asssss", "s");
select id, name, depart, length(depart) from L3; -- 如果配置了 PAD_CHAR_TO_FULL_LENGTH 不足长度的，会补足

• 严格模式和非严格模式的区别

  • 默认是严格模式
  • 可配置为非严格模式

• text

  • text 数据类型用于保存变长的大字符串，可以组多到 65535 ($2^{16}$ - 1) 个字符
  • 一般情况下，长文本会用 text类型，例如，文章，新闻等

create table L4(id int not null primary key auto_increment,title varchar(128),content text
) default charset=utf8;

• mediumtext

• 长度 16777215 ($2^{24}-1$) 个字符

• longtext
  • 长度 4294967295 or 4GB ($2^{32}$ - 1)

时间

• datetime

  • YYYY-MM-DD HH:MM:SS （1000-01-01 00:00:00 ~ 9999-12-31 23:59:59）

• timestamp

  • YYYY-MM-DD HH:MM:SS (1970-01-01 00:00:00 ~ 2037 年)
  • 对于 TIMESTAMP, 它把客户端插入的时间从当前时区转化为 UTC (世界标准时间) 进行存储
  • 查询时，将其又转化为客户端当前时区进行返回

举例

create table L5(id int not null primary key auto_increment,dt datetime,tt timestamp
) default charset=utf8;insert into L5(dt, tt) values("2023-12-12 12:12:12", "2023-12-12 12:12:12")select * from L5;

• 从上面显示的时间是一样的

  • 从定义上看，只是时间范围不一样而已
  • 但是，实际上，timestamp 内部存储和展示和mysql的时区设置也有关系
  • show varibales like '%time_zone%'

  +------------------+--------+
  | Variable_name    | Value  |
  +------------------+--------+
  | system_time_zone | CST    |
  | time_zone        | SYSTEM |
  +------------------+--------+
  

  • CST 指的是mysql所在主机的系统时间，是中国标准时间的缩写
    • China Standard Time UT+8:00
    • 就是 东八区的时间
    • 用的最多
  • 在插入 timestamp 类型的数据时，会将数据当成你现在所在时区(东八区)，内部会把这个时间转换成UTC时间进行存储
    • 再次进行查询的时候，会将UTC时间再转换成 东八区时间
  • 这里将时区改成 伦敦时间， set time_zone='+0:00'，再次查询时
    • datetime的时间是不会变化的，但是 timestamp 会变成 伦敦时区的时间，也就是 - 8h

• date

  • YYYY-MM-DD (1000-01-01 ~ 9999-12-31)

• time

  • HH:MM:SS (-838:59:59 ~ 838: 59: 59)

其他数据类型

• 其他数据类型: set, enum, TinyBlob, Blob, MediumBlob, LongBlob等
  • 详见官方文档 dev.mysql.com/doc/refman/5.7/en/data-types.html