mysql的数据类型_mysql 21111-CSDN博客

本文链接：https://blog.csdn.net/wozhangyi/article/details/107582245

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)