MySQL数据类型详解

豆豆豆豆变
317 阅读12分钟

MySQL中数据类型介绍

整数类型：bit、bool、tinyint、smallint、mediumint、int、bigint

浮点数类型：float、double、decimal

字符串类型：char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext

日期类型：Date、DateTime、TimeStamp、Time、Year

整数类型

类型字节数有符号值范围无符号值范围
tinyint[(n)] [unsigned]1[-2^7,2^7-1][0,2^8-1]
smallint[(n)] [unsigned]2[-2^15,2^15-1][0,2^16-1]
mediumint[(n)] [unsigned]3[-2^23,2^23-1][0,2^24-1]
int[(n)] [unsigned]4[-2^31,2^31-1][0,2^32-1]
bigint[(n)] [unsigned]8[-2^63,2^63-1][0,2^64-1]
  1. 默认都是有符号类型的，无符号的需要在类型后面跟上unsigned
  2. 无符号类型的，插入了负数会报错
  3. 超出范围会报错

类型(n)说明

在开发中，我们会碰到有些定义整型的写法是int(11)，int(N)我们只需要记住两点：

  • 无论N等于多少，int永远占4个字节
  • N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了unsigned zerofill才有效

ZEROFILL是一种只用于在整数类型字段上进行填充零的属性。该属性通常用于确保数字达到指定长度时，左侧用零进行填充。这在某些特定场景下非常有用，例如确保订单号或者其他标识符具有固定的长度。（使用很少）

注意在navicate或者phpmyadmin中显示数据显示可能不正确，如果你加了zerofill，查询出来的数据没有补充0，可以到mysql命令行中看下

看一下示例，理解ZEROFILL更方便：

  1. 先创建一个表test3
CREATE TABLE test3 (
       `a` int,
       `b` int(5),
       `c` int(5) unsigned,
       `d` int(5) zerofill,
       `e` int(5) unsigned zerofill,
       `f` int    zerofill,
       `g` int    unsigned zerofill,
       `h` bigint zerofill
     );
  1. 查看test3建表详情 show create table test3;，展示如下：
CREATE TABLE `test3` (
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  `c` int unsigned DEFAULT NULL,
  `d` int(5) unsigned zerofill DEFAULT NULL,
  `e` int(5) unsigned zerofill DEFAULT NULL,
  `f` int(10) unsigned zerofill DEFAULT NULL,
  `g` int(10) unsigned zerofill DEFAULT NULL,
  `h` bigint(20) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

原始的d字段用的是无符号的，可以看出当使用了zerofill自动会将有符号提升为无符号

insert into test3 values (1,1,1,1,1,1,1,1),(11,11,11,11,11,11,11,11),(12345,12345,12345,12345,12345,12345,12345,12345);

select * from test3;

image.png

int(5)输出宽度不满5时，前面用0来进行填充

int(n)中的n省略的时候，宽度为对应类型无符号最大值的十进制的长度，如bigint无符号最大值为2^64-1；长度是20位

浮点类型

类型字节大小范围（有符号）范围（无符号）用途
float[(m,d)]4(-3.402823466E+38,3.402823466351E+38)[0,3.402823466E+38)单精度 浮点数值
double[(m,d)]8(-1.7976931348623157E+308,1.797693134 8623157E+308)[0,1.797693134862315 7E+308)双精度 浮点数值
decimal[(m,d)]对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值

float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型。

浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。

float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。

create table test6(a float(5,2),b double(5,2),c decimal(5,2),d float,e double,f decimal);

查看test6建表详情 show create table test6;，展示如下：

CREATE TABLE `test6` (
  `a` float(5,2) DEFAULT NULL,
  `b` double(5,2) DEFAULT NULL,
  `c` decimal(5,2) DEFAULT NULL,
  `d` float DEFAULT NULL,
  `e` double DEFAULT NULL,
  `f` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

insert into test6 values (1,1,1,1,1,1),(2.1,2.1,2.1,2.1,2.1,2.1),(3.123,3.123,3.123,3.123,3.123,3.123),(4.125,4.125,4.125,4.125,4.125,4.125),(5.115,5.115,5.115,5.115,5.115,5.115),(6.126,6.126,6.126,6.126,6.126,6.126),(7.116,7.116,7.116,7.116,7.116,7.116),(8.1151,8.1151,8.1151,8.1151,8.1151,8.1151),(9.1251,9.1251,9.1251,9.1251,9.1251,9.1251),(10.11501,10.11501,10.11501,10.11501,10.11501,10.11501),(11.12501,11.12501,11.12501,11.12501,11.12501,11.12501);
select * from test6;

image.png

结果说明

decimal：采用的是四舍五入

float和double：采用的是四舍六入五成双

什么是四舍六入五成双？

就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后面是否还有不为0的任何数字，如果有，则直接进位，如果没有，需要看5前面的数字，若是奇数则进位，若是偶数则将5舍掉

说明：

a和b的数据正确插入，而c被截断了

浮点数float、double如果不写精度和标度，则会按照实际显示

decimal不写精度和标度，小数点后面的会进行四舍五入，并且插入时会有警告!

select sum(a),sum(b),sum(c),sum(d),sum(e),sum(f) from test6;

image.png 从上面sum的结果可以看出float、double会存在精度问题，decimal精度正常的，比如银行对统计结果要求比较精准的建议使用decimal。

日期类型

类型字节大小范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/‘838:59:59’HH:MM:转存失败，建议直接上传图片文件SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:转存失败，建议直接上传图片文件SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值，时间戳

字符串类型

类型范围存储所需字节说明
char(M)[0,m]，m的范围[0,2^8-1]m定产字符串
varchar(M)[0,m]，m的范围[0,2^16-1]m0-65535 字节
tinyblob0-255(2^8-1)字节L+1不超过 255 个字符的二进制字符串
blob0-65535(2^16-1)字节L+2二进制形式的长文本数据
mediumblob0-16777215(2^24-1)字节L+3二进制形式的中等长度文本数据
longblob0-4294967295(2^32-1)字节L+4二进制形式的极大文本数据
tinytext0-255(2^8-1)字节L+1短文本字符串
text0-65535(2^16-1)字节L+2长文本数据
mediumtext0-16777215(2^24-1)字节L+3中等长度文本数据
longtext0-4294967295(2^32-1)字节L+4极大文本数据

char类型占用固定长度，如果存放的数据为固定长度的建议使用char类型，如：手机号码、身份证等固定长度的信息。

表格中的L表示存储的数据本身占用的字节，L 以外所需的额外字节为存放该值的长度所需的字节数。

MySQL 通过存储值的内容及其长度来处理可变长度的值，这些额外的字节是无符号整数。

请注意，可变长类型的最大长度、此类型所需的额外字节数以及占用相同字节数的无符号整数之间的对应关系：

例如，MEDIUMBLOB 值可能最多2^24 - 1字节长并需要3 个字节记录其长度，3 个字节的整数类型MEDIUMINT 的最大无符号值为2^24 - 1。

数据类型选择的一些建议

  • 选小不选大：一般情况下选择可以正确存储数据的最小数据类型，越小的数据类型通常更快，占用磁盘，内存和CPU缓存更小。
  • 简单就好：简单的数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作代价要小得多，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。
  • 尽量避免NULL：尽量制定列为NOT NULL，除非真的需要NULL类型的值，有NULL的列值会使得索引、索引统计和值比较更加复杂。
  • 浮点类型的建议统一选择decimal
  • 记录时间的建议使用int或者bigint类型，将时间转换为时间戳格式，如将时间转换为秒、毫秒，进行存储，方便走索引

DML常见操作

drop，truncate，delete区别

  • drop (删除表)：删除内容和定义，释放空间，简单来说就是把整个表去掉

    drop语句将删除表的结构被依赖的约束（constrain），触发器（trigger）索引（index），依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

  • truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)，与drop不同的是，只是清空表数据而已。（删除表中的所有行，但表结构及其列、约束、索引等保持不变）

    注意：truncate不能删除具体行数据，要删就要把整个表清空了。

  • delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作。

    对于由foreign key约束引用的表，不能使用truncate table ，而应使用不带where子句的delete语句。由于truncate table 记录在日志中，所以它不能激活触发器。

    delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。

    truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

    如果有自增列，truncate方式删除之后，自增列的值会被初始化，delete方式要分情况（如果数据库被重启了，自增列值也会被初始化，数据库未被重启，则不变）

  • 如果要删除表定义及其数据，请使用 drop table 语句

  • 安全性：小心使用 drop 和 truncate，尤其没有备份的时候，否则哭都来不及

  • 删除速度，一般来说: drop> truncate > delete

droptruncatedelete
条件删除不支持不支持支持
删除表结构支持不支持不支持
事务的方式删除不支持不支持支持
触发触发器否否是

sql查询建议

  1. 建议别名前面跟上as关键字
  2. 查询数据的时候，避免使用select *，建议需要什么字段写什么字段
  3. like中的%可以匹配一个到多个任意的字符
  4. 空值查询需要使用IS NULL或者IS NOT NULL，其他查询运算符对NULL值无效
  5. 建议创建表的时候，尽量设置表的字段不能为空，给字段设置一个默认值
  6. <=>（安全等于）玩玩可以，建议少使用
  7. 在写分组查询的时候，最好按照标准的规范来写，select后面出现的列必须在group by中或者必须使用聚合函数。
  8. select语法顺序：select、from、where、group by、having、order by、limit，顺序不能搞错了，否则报错。

排序和分页（order by 、limit）

  1. order by … [asc|desc]用于对查询结果排序，asc：升序，desc：降序，asc|desc可以省略，默认为asc
  2. limit用来限制查询结果返回的行数，有2个参数（offset，count），offset：表示跳过多少行，count：表示跳过offset行之后取count行
  3. limit中offset可以省略，默认值为0
  4. limit中offset 和 count都必须大于等于0
  5. limit中offset和count的值不能用表达式
  6. 分页排序时，排序不要有二义性，二义性情况下可能会导致分页结果乱序，可以在后面追加一个主键排序

细说NULL导致的神坑，让人防不胜防

  1. NULL作为布尔值的时候，不为1也不为0
  2. 任何值和NULL使用运算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all），返回值都为NULL
  3. 当IN和NULL比较时，无法查询出为NULL的记录
  4. 当NOT IN 后面有NULL值时，不论什么情况下，整个sql的查询结果都为空
  5. 判断是否为空只能用IS NULL、IS NOT NULL
  6. count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行
  7. 当字段为主键的时候，字段会自动设置为not null
  8. NULL导致的坑让人防不胜防，强烈建议创建字段的时候字段不允许为NULL，给个默认值
avatar
文章
阅读
粉丝