4、MYSQL数据类型

FeiYi

MySQL

发布于：2019年12月4日

次浏览

字数：4.5k字

时长：20分钟

MySQL数据类型

如果在生产环境中mysql中存在有大量的数据库以及数据表，这个时候要找到某个表在哪个库中

在mysql的安装目录的数据目录下可以通过find找到关于表的数据文件

.frm 文件 //为表结构文件存放表中的结构

.MYI文件 //表示使用MyISAM存储引擎，用于存放数据的文件

.MYD //表示使用MyISAM存储引擎，用于存放表索引的文件

1、忘记表在哪个库中，怎么去查找

在information_schema库中存在一个名为TABLES的表，表中记录了所创建的表的各类信息

如果需要查找需要的数据表在哪个库中可以使用下列语句

mysql> select table_schema from TABLES where table_name='test5'\G        //table_schema（表所在的库）
*************************** 1. row ***************************
table_schema: sjk1120
1 row in set (0.00 sec)

2、忘记外键约束条件在哪个表中如何解决（TABLE_CONSTRAINTS ）

在information_schema库中存在名为TABLE_CONSTRAINTS的表，表中记录了包含主键，外键，唯一性约束条件在内的约束条件数据，如果需要找到外键，只需要把下列语句写入

mysql> select constraint_type,table_schema,table_name from table_constraints where constraint_type='foreign key';
/*在表中查看所在的库，表名，以及约束类型，当约束条件类型为外键时*/
+-----------------+--------------+------------+
| constraint_type | table_schema | table_name |
+-----------------+--------------+------------+
| FOREIGN KEY     | sjk1120      | test5      |
+-----------------+--------------+------------+
1 row in set (0.01 sec)

mysql数据类型

一旦决定了数据类型，就决定向字段中写入数据的格式

需要按照要求进行写入，否则写入不成功

一、数值类数据类型

默认创建数值类数据类型为有符号取值范围

如果需要创建无符号取值范围

语法：

ALTER TABLE 表名 MODIFY  字段名  数据类型  UNSIGNED; 
例：
mysql> ALTER TABLE test1 MODIFY id INT(11) UNSIGNED;

2）整数类数据类型

类型说明存储需求取值范围（有符号）

（1）TINYINT 极小的整数类型 1字节 -128~127

（2）SMALLINT 小的整数类型 2字节 -32768-32767

（3）MEDIUMINT 中等大小的整数类型 3字节 -8388608~8388607

（4）INT 普通整数类型 4字节 -2147483648~2147483647

（5）BIGINT 极大整数类型 8字节 -9223372036854775808~9223372036854775807

类型取值范围（有符号）

（1）TINYINT 0~255

（2）SMALLINT 0~65535

（3）MEDIUMINT 0~16777215

（4）INT 0~4294967295

（5）BIGINT 0~18446744073709551615

3）浮点类数据类型

浮点类数据类型存储需求

FLOAT(M,N) 单精度 4字节 M表示精度，表示保留的数的总数量，N为标度，表示保留的小数点位数

DOUBLE(M,N) 双精度浮点数 8字节

4）定点类数据类型

类型存储需求

DECIMAL(M,N) 定点数 M+2字节

定点数的取值范围，根据设定好的精度和标度确定，为对应到的位数上显示9

如果decimal不指定MN默认为（10，0)

mysql> create table test3(x float(5,1),y double(5,1),z decimal(5,1));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test3 values(5.12,5.15,5.123);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from test3;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|  5.1 |  5.2 |  5.1 |
+------+------+------+
1 row in set (0.00 sec)

5）日期时间类数据类型

类型格式存储需求取值范围

YEAR YYYY 1字节 1901~2155

TIME HH:MM:SS 3字节 -838:59:59~838:59:59（未来以及过去的35天）

DATE YYYY-MM-DD 3字节 1000-01-01~9999-12-31

DATETIME YYYY-MM-DD HH:MM:SS 8字节 1000-01-01 00:00:00~9999-12-31 23:59:59

TIMESTAMP YYYY-MM-DD HH:MM:SS 4字节 1970-01-01 00:00:00 UTC~2038-01-19 03:14:07 UTC

（1）YEAR数据类型

写入规则

如果是四位数方式进行写入正常识别

‘0’ - ‘69’ 被识别为2000-2069

‘70’ - ‘99’ 被识别为1970-1999

1-69被识别为2001-2069

70-99被识别为1970-1999

0被识别为0000

（2）TIME

写入规则：

如果写入的数据三组正常识别为HH:MM:SS

如果写入两组数据被识别为时分，如果写入一组数据被识别为秒

如果带有空格，空格前面被识别为天数，空格后如果有三组数据识别为时分秒，如果有两组数据识别为时分，如果有一组数据是被为时

mysql> CREATE TABLE test7(t TIME);
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO test7 VALUES('13:40:00'),(134001); /*写入的数据三组正常识别为HH:MM:SS*/
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test7;
+----------+
| t        |
+----------+
| 13:40:00 |
| 13:40:01 |
+----------+
2 rows in set (0.00 sec)
mysql> INSERT INTO test7 VALUES('13:41:10'),('10:10'),('2 10:10'),('3 02'),(10);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test7;
+----------+
| t        |
+----------+
| 13:41:10 |        /*13:41:10被正常识别*/
| 10:10:00 |        /*10:10为两组数据被识别为时分*/
| 58:10:00 |        /*2 10:10中带有空格，2被识别为天数，两天48小时+10小时10分=58:10*/
| 74:00:00 |        /*3 02中带有空格，3被识别为天数，3天72个小时，02倍识别为2小时，最后是72+2*/
| 00:00:10 |        /*10 只有一个数字，直接识别为秒*/
+----------+
5 rows in set (0.00 sec)

（3）DATE日期数据类型

写入规则

写入年份时如果以两位数形式写入

00-69或者’00’-‘69’被识别为2000-2069

70-99或者’70’-‘99’被识别为1970-1999

与year年份不同的是，00不作为特殊值识别为0000，而是识别为2000

mysql对于日期类型采用不严谨语法格式，即所有符号都可以作为分隔符使用

mysql> CREATE TABLE test8(d DATE);

mysql> INSERT INTO test8 VALUES('2019-11-22'),(20191122),(191122),(801122);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test8;
+------------+
| d          |
+------------+
| 2019-11-22 |        /*被正常识别*/
| 2019-11-22 |        /*被正常识别*/
| 2019-11-22 |        /*191122根据00-69或者'00'-'69'被识别为2000-2069，所以是2019-11-22*/
| 1980-11-22 |        /*801122根据70-99或者'70'-'99'被识别为1970-1999，所以是1980-11-22*/
+------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO test8 VALUES(000101),('000101');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test8;
+------------+
| d          |
+------------+
| 2019-11-22 |
| 2019-11-22 |
| 2019-11-22 |
| 1980-11-22 |
| 2000-01-01 |                /*这里是验证即使是00也会被正常识别为2000*/
| 2000-01-01 |                /*这里是验证即使是00也会被正常识别为2000*/
+------------+
6 rows in set (0.00 sec)

mysql> INSERT INTO test8 VALUES(CURRENT_TIME()),(NOW()),('19!03#01');
Query OK, 3 rows affected, 2 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 2

mysql> select * from test8;
+------------+
| d          |
+------------+
| 2019-11-22 |
| 2019-11-22 |
| 2019-11-22 |
| 1980-11-22 |
| 2000-01-01 |
| 2000-01-01 |
| 2019-11-22 |        /*current_time是时间函数，表示当前时间，会直接将当前时间以DATE的数据类型格式写入*/
| 2019-11-22 |        /*now同样是时间函数，表示当前时间，会直接将当前时间以DATE的数据类型格式写入*/
| 2019-03-01 |        /*19!03#01这里表示中间的符号，无论是什么符号，都会被识别为分隔符*/
+------------+
12 rows in set (0.00 sec)

（4）DATETIME日期类型

写入规则

写入年份时，如果以两位数形式写入，详情参考date类型

mysql> CREATE TABLE test9(dt DATETIME);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test9 VALUES(20190101010101),('2019-01-01 01:01:01'),('2019$01!01:01/01%01');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test9;
+---------------------+
| dt                  |
+---------------------+
| 2019-01-01 01:01:01 |        /*20190101010101，DATETIME数据类型，同样不加分隔符也能够识别*/
| 2019-01-01 01:01:01 |        /*正常识别*/
| 2019-01-01 01:01:01 |        /*2019$01!01:01/01%01，符号同样作为分隔符被识别*/
+---------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO test9 VALUES(NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test9;
+---------------------+
| dt                  |
+---------------------+
| 2019-01-01 01:01:01 |
| 2019-01-01 01:01:01 |
| 2019-01-01 01:01:01 |
| 2019-11-22 22:08:15 |        /*同样在使用now或者current_time函数会以datetime的时间类型写入*/
+---------------------+
5 rows in set (0.00 sec)

mysql> DELETE FROM test9;
Query OK, 6 rows affected (0.01 sec)

mysql> INSERT INTO test9 VALUES(000101010101),(701231010101),(600101122121);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test9;
+---------------------+
| dt                  |
+---------------------+
| 2000-01-01 01:01:01 |
| 1970-12-31 01:01:01 |
| 2060-01-01 12:21:21 |
+---------------------+
3 rows in set (0.00 sec)

（5）DATESTAMP日期数据类型

UTC世界标准时间（英国子午线标准）

可以设置时区来更改日期数据的显示内容，其他的日期类型，更改时区也不会更改时间

mysql> CREATE TABLE test10(ts TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test10 VALUES(010101010101),(NOW());
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test10;
+---------------------+
| ts                  |
+---------------------+
| 2001-01-01 01:01:01 |
| 2019-11-22 22:15:44 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SET TIME_ZONE='+10:00';                /*set time_zone='要更改的时区'*/
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test10;
+---------------------+
| ts                  |
+---------------------+
| 2001-01-01 01:01:01 |
| 2019-11-22 22:15:44 |
+---------------------+
2 rows in set (0.01 sec)

5.5)日期的加减运算

ADDDATE,DATE_ADD,DATE_SUB,SUBDATE,

DATEDIFF

语法：
SELECT DATEDIFF (日期格式或者日期字段名) 比较运算符(>,<,=)等
例：
SELECT * FROM employee WHERE DATEDIFF(NOW(),hireDate)>=3650;
/*查询表中当前日期和hireDate字段中的日期比较大于360天的*/
+------+--------+----------+---------+-----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job     | e_salary | hireDate   |
+------+--------+----------+---------+-----------+----------+------------+
| 1007 | CLARK  | m        |      10 | MANAGER   |     2450 | 2002-09-12 |
| 1009 | KING   | f        |      10 | PRESIDENT |     5000 | 1995-01-01 |
| 1001 | SMITH  | m        |      20 | CLERK     |      800 | 2005-11-12 |
| 1004 | JONES  | m        |      20 | MANAGER   |     2975 | 1998-05-18 |
| 1008 | SCOTT  | m        |      20 | ANALYSE   |     3000 | 2003-05-12 |
| 1011 | ADAMS  | m        |      20 | CLERK     |     1100 | 1999-01-05 |
| 1002 | ALLEN  | f        |      30 | SALESMAN  |     1600 | 2003-05-12 |
| 1003 | WARD   | f        |      30 | SALESMAN  |     1250 | 2003-05-12 |
| 1005 | MARTIN | m        |      30 | SALESMAN  |     1250 | 2001-06-12 |
| 1006 | BLAKE  | f        |      30 | MANAGER   |     2850 | 1997-02-15 |
| 1010 | TURNER | f        |      30 | SALESMAN  |     1500 | 1997-01-12 |
| 1012 | JAMES  | f        |      30 | CLERK     |      950 | 2008-06-15 |
+------+--------+----------+---------+-----------+----------+------------+

ADDDATE,DATE_ADD

日期加运算

语法：
SELECT ADDDATE(日期格式或者日期字段名,INTERVAL 增加的数值 数值单位)比较运算符(>,<,=)比较值（日期格式）;
数值单位：
YEAR    年
MONTH   月
DAY        日
WEEK    周
HOUR    时
MINUTE    分
SECOND    秒
HOUR_SECOND 时分秒   格式：INTERVAL '1:1:1' HOUR_SECOND
例：
mysql> SELECT * FROM employee WHERE ADDDATE(hireDate,INTERVAL 10 YEAR)<=DATE(NOW());
/*查询hireDate字段中的日期格式的每个值增加10年小于等于当前时间*/
用来查询大于10年的数据记录

SUBDATE,DATE_SUB

日期减运算

用法同加运算

6）字符串数据类型

（1）文本字符串

类型说明存储需求

CHAR(M) 固定长度的文本字符串 M字节 1<=M<=255

VARCHAR(M) 可变长度的文本字符串 L+1字节 1<=M<=21845

TINYTEXT 极小的文本字符串类型 L+1字节最多存放255个字符

TEXT 较小的文本字符串类型 L+2字节最多存放65535个字符

MEDIUMTEXT 中等大小的文本字符串类型 L+3字节最多存放16777215个字符

LONGTEXT 较长的文本字符串类型 L+4字节最多存放4294967295个字符

CHAR作为固定长的文本字符串，如果写入的数据超过指定的显示宽度，将会把多余的数据进行删除，如果写入的数据没有超过固定的显示长度，char将会在数据尾部填充空格达到指定的显示宽度，在显示时如果数据尾部有空格，将会把空格忽略

VARCAHR为可变长度的文本字符串，占用空间为L（写入的数据）+1字节（结束符），如果写入的数据超过显示宽度，和CHAR一样直接删除，如果写入的数据没有达到指定的显示宽度，不做任何操作

mysql> CREATE TABLE test12(c CHAR(4),v VARCHAR(30));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test12 VALUES('ab ','ab ');
mysql> select * from test12;
+------+------+
| c    | v    |
+------+------+
| ab   | ab   |
+------+------+
1 row in set (0.00 sec)

mysql> select CONCAT('(',c,')'),CONCAT('(',v,')') from test12;
+-------------------+-------------------+
| CONCAT('(',c,')') | CONCAT('(',v,')') |
+-------------------+-------------------+
| (ab)              | (ab )             |
+-------------------+-------------------+
1 row in set (0.01 sec)

ENUM(枚举)

在给定的范围内选择其中的一个值作为最后的结果

语法：

字段名 ENUM(‘v1’,’v2’,’v3’,….’vn’,) //括号内为值的范围，写入到枚举中的数据，每个数据称之为一个元素，每个元素都有编号，称之为索引下标，通过索引下标可以实现数据的写入，索引下标由1开始，每个元素拥有一个。如果写入的数据在给定的范围内没有，写入失败

每个枚举类型中可以写入65535个元素

mysql> CREATE TABLE test13(name varchar(30),grade decimal(7,1),level ENUM('excellent','good','bad'));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test13 VALUES('yanjiang','85','good'),('mi','90','excellent'),('rz','100',3);
    /*3为索引下标*/
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test13;
+----------+-------+-----------+
| name     | grade | level     |
+----------+-------+-----------+
| yanjiang |  85.0 | good      |
| mi       |  90.0 | excellent |
| rz       | 100.0 | bad       |
+----------+-------+-----------+
3 rows in set (0.00 sec)

SET

在给定的范围内选择零个或者多个值作为最后的结果

语法：

字段名 SET(‘v1’,’v2’,’v3’,….’vn’,) //和枚举相同的地方在于，每个元素都有一个索引下标，在set类型中最多可以写入64个元素，不适合使用索引下标进行写入范围内的值，如果写入的数据在给定的范围中不存在，写入失败，如果在set类型中写入重复值，会将重复的数据进行删除，且set会自动将数据进行排序。

mysql> CREATE TABLE test14(a SET('a','b','c','d','e'));                                     
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test14 VALUES('a'),('a,b,a'),('c,e,a');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test14;
+-------+
| a     |
+-------+
| a     |
| a,b   |
| a,c,e |
+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO test14 VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test14;
+-------+
| a     |
+-------+
| a     |
| a,b   |
| a,c,e |
| a     |
+-------+
4 rows in set (0.00 sec)

（2）二进制字符串

在二进制数据类型中，可以存放图片视频音频等数据

类型说明存储需求

BIT（M）位字段类型约为（M+7)/8字节

BINARY 固定长度的二进制字符串 M字节

VARBINARY 可变长度的二进制字符串 L+1字节

TINYBLOB 非常小的BLOB类型 L+1字节最大长度为255（单位：字节）

BLOB 小的BLOB类型 L+2字节最大长度为65535

MEDIUMBLOB 中等大小的BLOB类型 L+3字节最大长度为16777215

LONGBLOB 大的BLOB类型 L+4字节最大长度为4294967295

BIT

mysql> CREATE TABLE test15(b BIT(4));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test15 VALUES(2000);
ERROR 1406 (22001): Data too long for column 'b' at row 1        /*表示超出范围，
是因为他只能写入二进制的4位数字，如1111,1101,1001等算出来的十进制数*/

mysql> INSERT INTO test15 VALUES(15);        /*4个精度的bit数据类型，最大十进制数值就是15*/
Query OK, 1 row affected (0.00 sec)

mysql> select * from test15;            /*直接查看只能看到空白数据*/
+------+
| b    |
+------+
|     |
+------+
1 row in set (0.00 sec)


/*使用查看二进制字符串的方式*/
mysql> select bin(b) from test15;        /*查看二进制的显示方式，bin函数将二进制数据进行显示*/
+--------+
| bin(b) |
+--------+
| 1111   |
+--------+
1 row in set (0.00 sec)

BINARY

作为固定长度的二进制字符串

如果写入的数据没有达到指定的显示宽度，将会在数据的尾部填充\0达到指定的显示宽度，如果超过了显示宽度将会把多余的部分删除

mysql> CREATE TABLE test16(b BINARY(3),vb VARBINARY(30));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test16 VALUES(7,7);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM  test16;
+------+------+
| b    | vb   |
+------+------+
| 7    | 7    |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(b),LENGTH(vb) FROM test16;  /*length函数显示数据占用的字节长度*/
+-----------+------------+
| LENGTH(b) | LENGTH(vb) |
+-----------+------------+
|         3 |          1 |            /*虽然是相同的数值7，但是存储的长度不同，参考char和varchar*/
+-----------+------------+
1 row in set (0.01 sec)

VARBINARY

可变长读的二进制字符串

如果写入的数据没有达到指定的显示宽度，不做任何操作

清空表数据

（1）delete from 表名;
（2）truncate table 表名

两者都是清空表数据

不同点：

delete from 可以添加where条件判断，达到删除指定数据的目的

而truncate table，一旦写入就是清空所有表数据，不能添加where条件判断

通过truncate table操作表的速度要比delete速度快，且truncate占用的日志，事务资源少。使用delete清空的表只会抹除数据，不会释放表空间，而truncate释放表空间。

一旦建立有外键的父子表，不能使用truncate删除表数据，只能使用delete删除表数据

mysql> CREATE TABLE test6(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(30) NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test6(name) VALUES('a');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test6;        /*可以看到默认自增的id为1*/
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

mysql> select * from test6;        /*可以看到默认自增的id为2*/
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
+----+------+
2 rows in set (0.00 sec)

mysql> delete from test6;            /*使用delete清除表数据*/
Query OK, 2 rows affected (0.00 sec) 

mysql> select * from test6;            /*查看数据被清空*/
Empty set (0.00 sec)

mysql> INSERT INTO test6(name) VALUES('a');            /*重新加入数据*/
Query OK, 1 row affected (0.00 sec)

mysql> select * from test6;            /*发现数不是从id1开始自增，而是接着清除表数据之前的最后一个自增数据*/
+----+------+
| id | name |
+----+------+
|  3 | a    |
+----+------+
1 row in set (0.00 sec)

mysql> truncate table test6;        /*通过truncate来清空表数据*/
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test6(name) VALUES('a');            /*再次插入表数据*/
Query OK, 1 row affected (0.00 sec)

mysql> select * from test6;                /*查看时发现自增值重新从起始值1开始*/
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)
/*总结
使用truncate清理过的表会将自增数据的约束条件还原到开始位置
而delete清理过的表，之前的数据还会占用表空间*/