mysql数据类型

mysql数据类型

分类

整数型：tinyint、smallint、mediumint、int、bigint
浮点型：单精度float、双精度double、定点型decimal
日期和时间类型：year、date、time、datetime、timestamp
字符串类型：char、varchar
复合类型：枚举、set

优点

使系统能够根据数据类型来操作数据。
预防数据运算时出错。
更有效的利用空间。数据分类，可以使用最少的存储来存放数据，同时提高性能

int类型修饰符

unsigned 无符号整数，修饰符： 规定字段只能保存正的数据。它可以增大这个字段的正数支持的范围。
zerofill 修饰符： 规定0（不是空格 ） 填补输出的值。 使用这个值可以防止 mysql存储负值。
zerofill==unsigned+zerofill 无符号，不足的位补0
unsigned 无符号

unsgined和zero使用方法

mysql> create table haha (fi int,fiu int unsigned, fiz int zerofill,fiuz int unsigned zerofill);

查看表结构

mysql> desc haha;
fiz和fiuz字段值是一样的

mysql> show create table haha;
使用zerofill时默认给了unsigned

mysql> insert into haha values(10,10,10,10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into haha values(-10,-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'fiu' at row 1
mysql> insert into haha values(-10,10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'fiz' at row 1
mysql> insert into haha values (-10,10,100,-10);
ERROR 1264 (22003): Out of range value for column 'fiuz' at row 1
mysql> insert into kdata3 values (-10,10,100,1000);
Query OK, 1 row affected (0.00 sec)

可以看见在fiu,fiz和fiuz中存储负数，直接报错，因为unsgined和zerofill不允许使用负值，zerofill会自动添加unsgined，并且在数值前面补0

int(M) 在 integer 数据类型中，M 表示最大显示宽度。
在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。其实，除了显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。
如int的值为10
int（10）显示结果为0000000010
int（3）显示结果为010
就是显示的长度不一样而已 都是占用四个字节的空间，可以使用的空间也一样。当我们生成固定长度的序列号时，可以使用zerofill。
如：卡号，默认使用空格填充，不方便显示出来。现在以0来填充，查看一下显示的内容

mysql> create table azerofill (fi int(3), fiz int(3) zerofill,fiuz int(4) unsigned zerofill);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into azerofill values(11,11,11);
Query OK, 1 row affected (0.00 sec)
mysql> select * from azerofill;
+------+------+------+
| fi   | fiz  | fiuz |
+------+------+------+
|   11 |  011 | 0011 |
+------+------+------+
1 row in set (0.00 sec)

测试，插入超过显示范围的值

mysql>  insert into azerofill values(123456,123456,123456);
mysql> select * from azerofill;
+--------+--------+--------+
| fi     | fiz    | fiuz   |
+--------+--------+--------+
| 123456 | 123456 | 123456 |
+--------+--------+--------+
1 row in set (0.00 sec)

mysql> select * from azerofill;
+--------+--------+--------+
| fi     | fiz    | fiuz   |
+--------+--------+--------+
| 123456 | 123456 | 123456 |
|     16 |    012 |   0012 |
+--------+--------+--------+
2 rows in set (0.00 sec)


 发现位数不够用0补齐，超过设定值，正常显示
 

浮点型数据类型

float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型
MySQL浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度
float:单精度浮点型，占字节数为4，用32位二进制描述，有符号是7个有效位，无符号是8个有效位
double:双精度浮点型，占字节数为8，用64位二进制描述，有符号是15个有效位，无符号是16个有效位
decimal:数字型，用128位二进制描述，不存在精度损失，常用于银行帐目计算。（28个有效位）
精度是指计算机表达小数近似值的一种方式，单精度32位二进制，4个字节；双精度64位二进制，8个字节。
精度：decimal>double>float

float数值类型

整数位=有效数字-小数位
不管多少小数位，四舍五入后位数为指定值

mysql> create table haha(test float(5,2))；
创建一个表，字段test，字段类型为float(5,2)
有效数字5位，小数位2位，整数位3位
小数位无论多少位都会四舍五入为2位
mysql> insert into haha values(1234.123456789);
ERROR 1264 (22003): Out of range value for column ‘test’ at row 1
mysql> insert into haha values(123.123456789);
Query OK, 1 row affected (0.00 sec)

存储精确的小数：double和decimal数值类型

decimal、double要比float存储小数更精确

mysql> create table haha(tf float(10,2),td double(10,2),tdc decimal(10,2));
Query OK, 0 rows affected (0.01 sec)
mysql>  insert into haha values(1234567.89,1234567.89,1234567.89);
Query OK, 1 row affected (0.00 sec)
mysql> select * from haha\G
tf:1234567.88
td:1234567.89
tdc:1234567.89

丢失数据的原因是因为单精度类型float和双精度类型double在计算机中存储的时候，由于计算机只能存储二进制，所以浮点型数据在存储的时候，必须转化成二进制
如果一个float型数据转成二进制后的第32位之后都是0，那么数据是准的
如果一个float型数据转成二进制后的第32位之后不全为0，则数据就会存在误差
decimal类型是MySQL官方唯一指定能精确存储的类型，也是DBA强烈推荐和金钱相关的类型都要存储为decimal类型

字符串类型

char

char ：后面括号中必须有数值，来确认字符串的范围。 大小范围 ：0-255.
char(10) ; 指定了一个长度为10的字符值
新版本超过长度自动报错

mysql> create table hehe(name char(10));
mysql> insert into hehe values('abcdefghigklmn');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into hehe values('abcdefghig');
Query OK, 1 row affected (0.00 sec)
mysql> select * from hehe;
+------------+
| name       |
+------------+
| abcdefghig |
+------------+
1 row in set (0.00 sec)

binary

mysql> alter table hehe modify name char(9) binary;
mysql> insert into hehe values('ABC');
Query OK, 1 row affected (0.00 sec)

mysql> insert into hehe values('abc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from hehe where name="ABC";
+------+
| name |
+------+
| ABC  |
+------+
1 row in set (0.00 sec)

mysql> select * from hehe where name="abc";
+------+
| name |
+------+
| abc  |
+------+
1 row in set (0.00 sec)

使用select查看内容时，不需要where子句指定binary区分大小写，就可以，分别查看大小写内容

varchar：字符串可变长

mysql> create table xixi(aaa varchar(4),bbb char(4));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into xixi (aaa)values('');
Query OK, 1 row affected (0.00 sec)

mysql> insert into xixi (aaa)values('a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into xixi (aaa)values('ab');
Query OK, 1 row affected (0.00 sec)

mysql> insert into xixi (aaa)values('abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into xixi (aaa)values('abcd');
Query OK, 1 row affected (0.00 sec)

mysql> insert into xixi (aaa)values('abcde');
ERROR 1406 (22001): Data too long for column 'aaa' at row 1

varchar 0-65535
char 0-255

char和varchar的区别

从空间上考虑，用varchar合适；从效率上考虑，用char合适
当保存CHAR值时，在它们的右边填充空格以达到指定的长度
VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列定义的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充

字符串使用

1、VARCHAR型字段比CHAR型字段占用更少的内存和硬盘空间。当你的数据库很大时，这种内存和磁盘空间的节省会变得非常重要.
2、虽然VARCHAR使用起来较为灵活，但是从整个系统的性能角度来说，CHAR数据类型的处理速度更快，有时甚至可以超出VARCHAR处理速度的50%。
所以在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡。

日期和时间类型

date 日期

年-月-日

mysql> create table haha(birthday date);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into haha values(19970101),('1997-07-22');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into haha values(200116);
Query OK, 1 row affected (0.00 sec)

mysql> select * from haha;
+------------+
| birthday   |
+------------+
| 1997-01-01 |
| 1997-07-22 |
| 2020-01-16 |
+------------+
3 rows in set (0.00 sec)

time时间

时：分：秒

mysql> create table hehe(showtime time);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into hehe values('11:11:11'),('11:19'),(131419);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into haha values(14),(1413);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from hehe;
+----------+
| showtime |
+----------+
| 11:11:11 |
| 11:19:00 |
| 13:14:19 |
| 00:00:14 |
| 00:14:13 |
+----------+
5 rows in set (0.00 sec)

year

年
year ： 00-69自动转为： 2000-2069 , 70-99自动转为1970-1999

mysql> create table heihei(test year);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into heihei values(09),(4),(69),(70),(2099);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from heihei;
+------+
| test |
+------+
| 2009 |
| 2004 |
| 2069 |
| 1970 |
| 2099 |
+------+
5 rows in set (0.00 sec)

datetime或timestamp

datetime类型能保存的最大范围的值为1001年到9999年，精度为秒，它把日期和时间封装到格式为YYYY-MM-DD HH:MM:SS的整数中，与时区无关，使用8个字节的存储空间，年-月-日 时：分：秒
timestamp类型保存了从1970年1月1日（格林尼治时间）以来的秒数，它和linux的时间戳相同，只是用了4个字节的存储空间，因此它的范围比datetime的范围小了很多，只能表示从1970年到2038年，我们可以使用它提高空间利用率，年-月-日 时：分：秒

mysql> create table xixi(test_datetime datetime,test_timestamp timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into xixi values('1999-11-11 23:23:23',19991111232323);
Query OK, 1 row affected (0.00 sec)

mysql> insert into xixi (test_datetime)values(961122154913);
Query OK, 1 row affected (0.00 sec)

mysql> select * from xixi;
+---------------------+---------------------+
| test_datetime       | test_timestamp      |
+---------------------+---------------------+
| 1999-11-11 23:23:23 | 1999-11-11 23:23:23 |
| 1996-11-22 15:49:13 | 2021-01-16 18:42:57 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

复合类型

它们字段的值，必须从预先定义好的字符串集合中选取。
ENUM（枚举）：只能取一个，用于互斥。男人，女人。
set ： 能取多个。

枚举

mysql> create table hengheng(sex enum('w','m'));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into hengheng values('W'),('w'),('M'),('hengheng'),
('null');ERROR 1265 (01000): Data truncated for column 'sex' at row 4

mysql> insert into hengheng values('W'),('w'),('M');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

set

mysql> create table oo(choose set('a','b','c','d','e'));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into oo values('a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into oo values('a,b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into oo values('a,b,c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into oo values('a,a,a,b');
Query OK, 1 row affected (0.00 sec)
##重复选项无效，会自己去重

mysql> insert into oo values('f');
ERROR 1265 (01000): Data truncated for column 'choose' at row 1