MySQL 数据类型
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
数值型:
整型
小数:
定点数
浮点数
特殊:bit bool
字符型:
较短的文本:char、varchar
较长的文本:text、blob(较长的二进制数据)
日期型:
整型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
tinyint(m) | 1 byte | (-128,127) | (0,255) | 小整数值 |
smallint(m) | 2 bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
mediumint(m) | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
int(m) | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
bigint(m) | 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
使用细节
- 如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字
- 如果插入的数值超出了整型的范围,会报out of range异常,并且插入临界值
- 如果不设置长度,会有默认的长度,长度代表了显示的最大宽度,如果不够会用0在左边填充,使用参数必须搭配zerofill使用
整形取值范围详解
以tinyint()为例,MySQL下用1个字节表示tinyint,也就是8位。tinyint的取值范围是:带符号的范围是-128到127,无符号的范围是0到255。
-
无符号的最小值即全部8位都为0,换算成十进制就是0,所以无符号的tinyint的最小值为0。无符号的最大值即全部8bit都为1,
11111111=255 (2^8-1)
-
在计算机中,用最高位表示符号。0表示正,1表示负,剩下的表示数值。最小值:
11111111=-127 (2^7-1)
表示负值;最大值:01111111 = +127 (2^7-1)
表示正值。 -
有符号的tinyint的最小值是-128的原因是:在计算机中,表示负值是用补码。虽然“-0”也是“0”,但根据正、反、补码体系,“-0”的补码和“+0”是不同的,这样就出现两个补码代表一个数值的情况。为了将补码与数字一一对应,所以人为规定“0”一律用“+0”代表。同时为了充分利用资源,就将原来本应该表示“-0”的补码规定为代表-128
参数及zerofill的说明
- int(4) 的意思是如果给的数不足4位,前面的位数就用0填充,需要配合zerofill 使用,否则没有意义
- 若只指定 zerofill 则范围自动变为无符号 unsigned
//创建表
mysql> create table test(
-> unm1 int,
//int(4) 和 zerofill 分别代表什么?
-> unm2 int(4) zerofill,
-> unm3 int(6) unsigned zerofill
-> );
Query OK, 0 rows affected (0.58 sec)
//插入数据
mysql> insert into test value(21,21,21);
Query OK, 1 row affected (0.03 sec)
mysql> insert into test value(21111,21111,21111);
Query OK, 1 row affected (0.04 sec)
//查看表
mysql> select * from test;
+-------+-------+--------+
| unm1 | unm2 | unm3 |
+-------+-------+--------+
| 21 | 0021 | 000021 |
| 21111 | 21111 | 021111 |
+-------+-------+--------+
2 rows in set (0.00 sec)
浮点型
MySQL数据类型 | 含义 |
---|---|
float(m,d) | 单精度浮点型 8位精度(4字节) m显示长度,d小数位数 |
double(m,d) | 双精度浮点型 16位精度(8字节) m显示长度,d小数位数 |
小数类型 float
- float(m,d),m指定显示长度,d指定小数位数,占用4个字节,如果m和d被省略,根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。
- 设一个字段定义为float(6,3),如果插入123.45678,实际数据库里存的是123.457,如果插入12.12,存储的是12.1200
- float(4,2) 表示的范围是 -99.99~99.99
- float(4,2) unsigned 表示的范围是 0~99.99
//创建无符号小数 范围 -99.99~99.99
mysql> create table test4 (id int, salary float(4,2));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into test4 values(100,-99.99);
Query OK, 1 row affected (0.06 sec)
mysql> insert into test4 values(100,-99.991);
Query OK, 1 row affected (0.04 sec)
mysql> insert into test4 values(100,99.99);
Query OK, 1 row affected (0.03 sec)
mysql> insert into test4 values(100,99.991);
Query OK, 1 row affected (0.07 sec)
mysql> select * from test4;
+------+--------+
| id | salary |
+------+--------+
| 100 | -99.99 |
| 100 | -99.99 |
| 100 | 99.99 |
| 100 | 99.99 |
+------+--------+
4 rows in set (0.00 sec)
//为何这里报错 因为四舍五入
mysql> insert into test4 values(100,99.995);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into test4 values(100,100);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
定点数 decimal
浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值
基本语法
- decimal(m,d),m指定显示长度,d指定小数位数,d不能超过m,如果被省略,m默认为10,d默认为0
- decimal(4,2) 表示的范围是 -99.99~99.99
- decimal(4,2) unsigned 表示的范围是 0~99.99
float 和 decimal的精度不一样
- 单精度浮点数精确到大约7位小数位(根据硬件)
- decimal整数最大位数m为65,支持的十进制数的最大位数d是30,精确度较高
- 如果精度不高小数点7位以下,可以用float,如果货币数据可以用decimal
mysql> create table test5 (id int, salary float(10,8), salary2 decimal(10,8));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into test5 value(10, 23.123456578, 23.12345678);
Query OK, 1 row affected (0.03 sec)
mysql> select * from test5;
+------+-------------+-------------+
| id | salary | salary2 |
+------+-------------+-------------+
| 10 | 23.12345695 | 23.12345678 |
+------+-------------+-------------+
1 row in set (0.00 sec)
字符型
MySQL数据类型 | 含义 |
---|---|
char(n) | 固定长度,最多255个字符 |
varchar(n) | 固定长度,最多65535个字符 |
tinytext | 可变长度,最多255个字符 |
text | 可变长度,最多65535个字符 |
mediumtext | 可变长度,最多2的24次方-1个字符 |
longtext | 可变长度,最多2的32次方-1个字符 |
较短的文本:char、varchar,较长的文本:text
基本介绍
- char(m) 定长字符串类型,m是字符, m最大255字符
- varchar(m) 变长字符串类型,m是字符, m换算成字节最大65532字节
- text和 varchar 一样,char和varchar可以有默认值,text不能指定默认值default
char 的使用
//char(2) 表示可以存放2个字符,中英文都行
mysql> create table test6 (id int,name char(2));
Query OK, 0 rows affected (0.08 sec)
mysql> insert into test6 values(11,'中国');
ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xB9\xFA' for column 'name' at row 1
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test6 values(11,'中国');
Query OK, 1 row affected (0.04 sec)
mysql> insert into test6 values(11,'ab');
Query OK, 1 row affected (0.03 sec)
mysql> select * from test6;
+------+------+
| id | name |
+------+------+
| 11 | 中国 |
| 11 | ab |
+------+------+
2 rows in set (0.00 sec)
//超过3个字符存不进去
mysql> insert into test6 values(11,'abc');
ERROR 1406 (22001): Data too long for column 'name' at row 1
varchar 的使用
//varchar(4) 表示可以存放4个字符,中英文都行,注意不是字节
mysql> create table test8 (id int, name varchar(4));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test8 values(10,'沉梦昂志');
Query OK, 1 row affected (0.02 sec)
mysql> select * from test8;
+------+----------+
| id | name |
+------+----------+
| 10 | 沉梦昂志 |
+------+----------+
1 row in set (0.00 sec)
//超过4个字符存不进去
mysql> insert into test8 values(10,'沉梦昂志1');
ERROR 1406 (22001): Data too long for column 'name' at row 1
关于varchar(n), n 到底可以是多大,这个n值,和你表的编码有关
- varchar长度可以指定为0到65,535之间的字节, 但是 有1-3个字节用于记录数据大小. 所以说有效字节数 65532
- 如果表的编码utf8时,在utf8中,一个汉字占用3个字节,系统需要考虑全部汉字的情况,varchar(n) n最大是 65532/3 = 21844
- 如果表的编码gbk,在gbk 中,一个汉字占用2个字节,系统需要考虑全部汉字的情况,varchar(n) n最大是 65532/2 = 32766
一个字段的长度全部加起来不能超过65535个字节
//int 4 + varchar(21844)*3 + 3 > 65535 报错
mysql> create table test9 (id int, name varchar(21844)) charset=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
//int 4 + varchar(21842)*3 + 3 < 65535
mysql> create table test9 (id int, name varchar(21842)) charset=utf8;
Query OK, 0 rows affected (0.10 sec)
//varchar(21844)*3 + 3 = 65535
mysql> create table test10 (name varchar(21844)) charset=utf8;
Query OK, 0 rows affected (0.08 sec)
//varchar(21845)*3 + 3 > 65535 报错
mysql> create table test10 (name varchar(21845)) charset=utf8;
ERROR 1050 (42S01): Table 'test10' already exists
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类型的快
char 和 varchar 对字串后面空格处理的机制说明
- char(m) 比指定长度大的值将被截短,而比指定长度小的值将会用空格作填补。所以char类型存储的字符串末尾不能有空格,varchar不受此限制
- 如果char中存放’aa ‘,取出来就是’aa’,如果你确实需要在字符串的最后保存 空格,请使用 varchar
如何选择char 和 varchar
- 如果数据是定长,推荐使用char,比如md5的密码,邮编,手机号,身份证等
- 如果数据长度不确定,推荐使用varchar,比如留言,文章
- 查询速度,char > varchar
varchar 和 text
- varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),text是实际字符数+2个字节
- text类型不能有默认值。,一般用于存放大段文本
- varchar可直接创建索引,text创建索引要指定前多少个字符
- varchar查询速度快于text,在都创建索引的情况下,text的索引似乎不起作用。
//text不能指定默认值default
mysql> create table test12 (id int, content text not null default ' ');
ERROR 1101 (42000): BLOB/TEXT column 'content' can't have a default value
mysql> create table test12 (id int, content text not null);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into test12 values(1, '这是留言内容');
Query OK, 1 row affected (0.04 sec)
mysql> select * from test12;
+------+--------------+
| id | content |
+------+--------------+
| 1 | 这是留言内容 |
+------+--------------+
1 row in set (0.00 sec)
效率来说基本是char>varchar>text,但是如果使用的是Innodb引擎的话,推荐使用varchar代替char
char和varchar可以有默认值,text不能指定默认值
日期和时间
MySQL数据类型 | 含义 |
---|---|
date | 日期 ‘2008-12-2’ |
time | 时间 ‘12:25:36’ |
datetime | 日期时间 ‘2008-12-2 22:06:44’ |
timestamp | 自动存储记录修改时间 |
日期格式:
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
YEAR 0000 YYYY 年 '2015'
DATE 0000-00-00 YYYY-MM-DD 日期 '2015-04-24'
TIME 00:00:00 HH:MM:SS 时间'12:25:36'
DATETIME 0000-00-00 00:00:00 YYYY-MM-DD HH:MM:SS 日期时间'2015-04-24 22:06:44'
TIMESTAMP 0000-00-00 00:00:00 日期时间'2020-07-27 14:00:00' 不固定,随记录的更新而更新
timestamp比较特殊,如果定义一个字段的类型为timestamp,这个字段的时间会在dml的时候自动刷新。
timestamp数据类型的字段可以存放这条记录最后被修改的时间,而不是真正来的存放时间。
代码示例
mysql> create table birthday (t1 date, t2 datetime, t3 timestamp);
Query OK, 0 rows affected (0.56 sec)
mysql> insert into birthday (t1,t2) values ('2000-11-11', '2001-11-11 12:12:12');
Query OK, 1 row affected (0.06 sec)
mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 2000-11-11 | 2001-11-11 12:12:12 | 2020-07-27 14:00:00 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)
//修改数据时(更新 添加),timestamp字段的会自动更新为当前时间
mysql> update birthday set t1 = '1999-11-11' where t1='2000-11-11';
Query OK, 1 row affected (0.50 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 1999-11-11 | 2001-11-11 12:12:12 | 2020-07-27 14:06:24 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)
BIT 类型
基本语法
BIT(M)代表可以存储M个bit,M的取值范围为1到64,如果M被省略,默认为1
案例说明
mysql> create table test2(id int, num bit(8));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into test2 values(10,10);
Query OK, 1 row affected (0.05 sec)
mysql> insert into test2 value(10,65);
Query OK, 1 row affected (0.04 sec)
//bit 在显示的时候,就是以对应的ascii 字符,10是换行
mysql> select * from test2;
+------+------+
| id | num |
+------+------+
| 10 |
|
| 10 | A |
+------+------+
2 rows in set (0.00 sec)
如果我们有这样的值, 只存放 0 或 1, 我们可以定义一个 bit(1) 这样可以节省空间
mysql> create table test3 (id bit(1));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into test3 values(0);
Query OK, 1 row affected (0.04 sec)
mysql> insert into test3 values(1);
Query OK, 1 row affected (0.03 sec)
mysql> select * from test3;
+------+
| id |
+------+
| |
| |
+------+
2 rows in set (0.00 sec)
Enum 和 SET 类型
enum 单选
单选字符串数据类型,适合存储表单界面中的“单选值”。
设定enum的时候,需要给定“固定的几个选项”;存储的时候就只存储其中的一个值。
基本语法:
- enum(“选项1”,“选项2”,“选项3”,…);
- 实际上,enum的选项都会对应一个数字,依次是1,2,3,4,5…,最多有65535个选项
- 使用的时候,可以使用选项的字符串格式,也可以使用对应的数字。
set 多选
多选字符串数据类型,适合存储表单界面的“多选值”。
设定set的时候,同样需要给定“固定的几个选项”;存储的时候,可以存储其中的若干个值。
基本语法:
- set(“选项1”,“选项2”,“选项3”,…)
- 同样的,set的每个选项值也对应一个数字,依次是1,2,4,8,16…,最多有64个选项
- 使用的时候,可以使用set选项的字符串本身(多个选项用逗号分隔),也可以使用多个选项的数字之和(比如:1+2+4=7)
mysql> create table votes(
-> username varchar(60) not null default '',
-> hobby set('苹果', '菠萝', '西瓜') not null,
-> sex enum('男', '女') not null
-> ) charset=utf8;
mysql> insert into votes values('张三', '西瓜', '男');
Query OK, 1 row affected (0.03 sec)
//set添加两个选项时 不能有空格
mysql> insert into votes values('张三', '西瓜,苹果', '男');
Query OK, 1 row affected (0.03 sec)
/* 可以用数字代替 */
mysql> insert into votes values('李四', '1', '2');
Query OK, 1 row affected (0.04 sec)
//这里3 添加的是 苹果,菠萝,而不是西瓜,如何添加西瓜呢?
mysql> insert into votes values('李四', '3', '2');
Query OK, 1 row affected (0.04 sec)
//这里4 添加的是 西瓜
mysql> insert into votes values('李四', '4', '2');
Query OK, 1 row affected (0.42 sec)
mysql> select * from votes;
+----------+-----------+-----+
| username | hobby | sex |
+----------+-----------+-----+
| 张三 | 西瓜 | 男 |
| 张三 | 苹果,西瓜 | 男 |
| 李四 | 苹果 | 女 |
| 李四 | 苹果,菠萝 | 女 |
| 李四 | 西瓜 | 女 |
+----------+-----------+-----+
5 rows in set (0.00 sec)
//错误写法
mysql> insert into votes values('张三', '冬瓜', '男');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
mysql> insert into votes values('张三', '西瓜, 苹果', '男');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
mysql> insert into votes values('李四', '1,2', '2');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
SET 的查询方式
查询Set类型中的苹果,则查到只喜欢苹果的,而同时喜欢苹果和西瓜的查不到,显然不合理
需要配合find_in_set()函数来查询,find_in_set()函数查到会返回第一个字符串在第二个字符串列表中的位置(从1开始计算),查不到返回0
find_in_set()函数用法
mysql> select find_in_set('a','a,b,c');
+--------------------------+
| find_in_set('a','a,b,c') |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.46 sec)
mysql> select find_in_set('b','a,b,c');
+--------------------------+
| find_in_set('b','a,b,c') |
+--------------------------+
| 2 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select find_in_set('d','a,b,c');
+--------------------------+
| find_in_set('d','a,b,c') |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select find_in_set('aa','a,b,c');
+---------------------------+
| find_in_set('aa','a,b,c') |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (0.00 sec)
Set 的查询需配合find_in_set()函数
mysql> select * from votes;
+----------+-----------+-----+
| username | hobby | sex |
+----------+-----------+-----+
| 张三 | 西瓜 | 男 |
| 张三 | 苹果,西瓜 | 男 |
| 李四 | 苹果 | 女 |
| 李四 | 苹果,菠萝 | 女 |
| 李四 | 西瓜 | 女 |
| 李四 | 西瓜 | 女 |
+----------+-----------+-----+
6 rows in set (0.00 sec)
mysql> select * from votes where sex=1;
+----------+-----------+-----+
| username | hobby | sex |
+----------+-----------+-----+
| 张三 | 西瓜 | 男 |
| 张三 | 苹果,西瓜 | 男 |
+----------+-----------+-----+
2 rows in set (0.44 sec)
//Enum 用这种查询方式显然不合理
mysql> select * from votes where hobby=1;
+----------+-------+-----+
| username | hobby | sex |
+----------+-------+-----+
| 李四 | 苹果 | 女 |
+----------+-------+-----+
1 row in set (0.00 sec)
mysql> select * from votes where hobby='苹果';
+----------+-------+-----+
| username | hobby | sex |
+----------+-------+-----+
| 李四 | 苹果 | 女 |
+----------+-------+-----+
1 row in set (0.00 sec)
//借助 find_in_set() ,返回值为非0 则符合,返回值为0 不符合,这里'苹果'不能用数字表示
mysql> select * from votes where find_in_set('苹果', hobby);
+----------+-----------+-----+
| username | hobby | sex |
+----------+-----------+-----+
| 张三 | 苹果,西瓜 | 男 |
| 李四 | 苹果 | 女 |
| 李四 | 苹果,菠萝 | 女 |
+----------+-----------+-----+
3 rows in set (0.00 sec)