MySQL(一)：数据类型-优快云博客

本文链接：https://blog.youkuaiyun.com/2501_90392025/article/details/145465064

CREATE TABLE t2(

a DATETIME

)

//插入微妙级别的数据

INSERT INTO t2 SELECT ‘2011-01-01 00:01:10.123456’;

//查询插入的数据

SELECT a FROM t2;

在这里插入图片描述不过mysql数据库提供了函数MICROSECOND函数来提取日期中的微秒值。

SELECT MICROSECOND(‘2011-01-01 00:01:10.123456’);

在这里插入图片描述 mysql的CAST函数在强制转换到DATETIME时也会截断毫秒数，在插入之后同样会截断。

mysql从5.6.4版本开始，mysql增加了对秒的小数部分（fractional second)的支持，其具体语法是type_name(fsp)。

其中，type_name可以是TIME、DATETIME、和TIMESTAMP。fsp表示支持秒的小数部分的精度，最大为6，精确到微秒；默认是为0，表示没有小数部分，同时这也是为了兼容之前的DATETIEM和TIMESTAMP的用法，而对于一些时间函数，也增加了对fsp的支持，比如CURTIME()、SYSDATE()和UTC_TIMESTAMP()。

SELECT CURTIME(4) AS ‘time’;

在这里插入图片描述 #### TIMESTAMP

TIMESTAMP和DATETIME显示的效果是一样的，都是固定的YYYY-MM-DD HH:MM:SS类型，但TIMESTAMP只有4个字节，那是因为显示范围的不同，TIMESTAMP的显示范围是从UNIX的时间纪元开始（1970-01-01 00:00:00 UTC）到2038-01-19 03:14:07 UTC（Integer类型是4个字节，也就是32位，能表示的最长时间是68年，UTC是世界统一时间），而其实际存储的内容是1970-01-01 00:00:00到当前时间的毫秒数。

TIMESTAMP和DATETIME的不同

在建表时，列为TIMESTAMP类型的可以设置默认值而DATETIME不行

//虽然执行这条语句不会报错，但表中的a列信息还是没有默认值

CREATE TABLE t3(

a DATETIME DEFAULT ‘2011-01-01 00:01:10’

)

//不会报错，且有默认值信息

CREATE TABLE t4(

b TIMESTAMP DEFAULT ‘2011-01-01 00:01:10’

)

在更新表时，可以设置TIMESTAMP类型的列自动更新为当前时间

CREATE TABLE t5(

a INT ,

b TIMESTAMP DEFAULT CURRENT_TIMESTAMP

)

INSERT INTO t5(a) VALUES(1);

SELECT * FROM t5;

//执行UPDATE时更新为当前时间的例子

CREATE TABLE t6(

a INT,

//设置b字段为update操作时，修改为当前时间

b TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

)

INSERT INTO t6 SELECT 1,CURRENT_TIMESTAMP;

//过一段时间

UPDATE t6 SET a = 2 WHERE a = 1;

SELECT * FROM t6;

但要注意的是，要发生数据变化才会修改，即如果执行update操作，但数据还是不变，那么也不会进行更新的。原本是可以在建表时将TIMESTAMP的列设为一个默认值为更新时的时间，已经取消了。

YEAR和TIME

YEAR类型只会占用一个字节(也就是8位，可以储存范围为0~255)，并且在定义时可以指定显示的宽度为YEAR(4)或者YEAR(2)（现在YEAR(2)已经被删除了，所以下面只讨论YEAR(4)）。

YEAR(4)其显示年份为1901~2155

TIME类型占用3个字节，显示的范围为-839:59:59~838:59:59，TIME在时位上可以超过23，因为它不但可以表示一天的时间，也可以表示时间间隔（几天或者一个月）,同时这也表示了为什么会有负数，TIME和DATETIME类型一样，也可以显示微秒时间MISCOPESECOND()，也一样会被掐断。

与日期相关的函数

NOW、CURRENT_TIMESTAMP和SYSDATE

这三个函数都是返回当前时间，并且有一点区别

SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE();

SLEEP前

在这里插入图片描述 SLEEP后

在这里插入图片描述 SYSDATE明显睡了两秒，也就是说，返回的是执行到函数的时间

时间加减函数

无论是时间类型还是时间函数类型，都不可以简单使用加减号来进行运算,如果使用加减号来进行运算，会发现只是简单地按位对应来加减而已，没有返回正确的时间类型。

CREATE TABLE t7(

a TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

b TIMESTAMP DEFAULT CURRENT_TIMESTAMP

)

INSERT INTO t7 VALUES();

SELECT a+b,a,b FROM t7;

SELECT NOW(),NOW()+0;

在这里插入图片描述

在这里插入图片描述具体使用过的方法是DATE_ADD(date,INTERVAL expr unit)和DATE_SUB(date,INTERVAL,expr,unit)，分别是假发和减法(INTERVAL是间隔的意思)

SELECT NOW() AS cur,DATE_ADD(NOW(),INTERVAL 1 DAY) AS tomorrow,DATE_SUB(NOW(),INTERVAL 1 DAY) AS yesterday;

在这里插入图片描述其中expr值可以是hi一个负数，因此单纯使用DATE_ADD也可以完成DATE_SUB的操作。

SELECT NOW() AS cur,DATE_ADD(NOW(),INTERVAL -1 DAY) AS yesterday;

在这里插入图片描述如果是闰月的话（比如不是闰年的2月28日加上4年，就会变成闰年的2月29日），DATE_ADD函数返回的日期是2月29日，如果不是闰月，那么返回的日期是2月28日。

SELECT DATE_ADD(‘2000-02-29’,INTERVAL 4 YEAR);

在这里插入图片描述

SELECT DATE_ADD(‘2000-02-29’,INTERVAL 5 YEAR);

在这里插入图片描述除了DAY和YEAR之外，还有其他类型的，比如MICROSECOND(微秒会被截断)、SECOND(秒)、MINUTE(分钟)、HOUR、WEEK、MONTH等类型

DATE_FORMAT函数

按照用户的需求格式化打印出日期

SELECT DATE_FORMAT(NOW(),“%Y%m%d”);

在这里插入图片描述

数字类型

整形

整形类型有INT、SMALLINT、TINYINT、MEDIUMINT和BIGINT。

| 类型 | 占用空间（字节） | 最小值 | 最大值 |

| — | — | — | — |

| TINYINT | 1 | -128（有符号位） | 255 |

| SMALLINT | 2 | -32768(-2^15) | 2^16 |

| MEDIUNINT | 3 | -2^23 | 2^24 |

| INT | 4 | -2^31 | 2^32 |

| BIGINT | 8 | -2^63 | 2^64 |

对于整形，前面有两个相关的属性就是ZEROFILL和UNSIGNED，一旦开启了ZEROFILL那么UNSIGNED也会自动开启。

浮点型（非精确类型）

Mysql支持两种浮点类型，单精度的FLOAT类型以及双精度的DOUBLE PRECISION类型。这两种类型都是非精确的类型，即经过一些操作之后并不能保证运算的准确性，例如M*G/G不一定会等于M。

FLOAT类型用于表示近似数值数据类型，SQL标准允许在关键字FLOAT后面的括号内用位来指定精度（但不能为指数范围）。Mysql还支持可选的只用于确定存储大小的精度规定，0到23的精度对应FLOAT列的4字节单精度，24到53的精度对应DOUBLE列的8字节双精度。

Mysql还允许使用非标准语法，FLOAT(M,D)或REAL(M.D)或DOUBLE PRECISION(M,D)。这里的(M,D)表示该值一共显示M位整数，其中D位是小数点后面的位数。例如FLOAT(7,4)的一个列可以显示为-999.9999。如果超出了范围，Mysql保存值时会进行四舍五入，比如在FLOAT(7,4)中插入999.00009，会变成999.0001。

Mysql将DOUBLE视为DOUBLE PRECISION(非标准扩展)的同义词，将REAL视为DOUBLE PRECISION（非标准扩展）的同义词，即REAL和DOUBLE都是DOUBLE PRECISION，若将Mysql服务器的模式设置，REAL_AS_FLOAT，那这时REAL会被视为FLOAT类型。

为了保证最大的可以可移植性（保证精确性，因为如果不满长FLOAT参数里面的长度会进行补充），需要使用近似数值数据值存储的代码，使用FLOAT或DOUBLE PRECISION，并不规定精度或者位数。

高精度类型

DECIMAL和NUMERIC类型在Mysql中被视为相同的类型，用于保存必须为确切精度的值，比如工资，当声明该类型的列时，通常必须指定精度和标度（精度指数字位数即保存值的主要位数，标度指小树点后面可以保存的位数），例如 salary DECIMAL(5,2) ，精度为5，标度为2。在标准SQL中，语法DECIMAL(M)等价于DECIMAL(M,0)，M默认是10的。

CREATE TABLE t10(

a DECIMAL

)

SHOW CREATE TABLE t10;

在这里插入图片描述 DECIMAL或NUMERIC的最大位数是65位，但具体的DECIMAL或NUMERIC列的实际范围受具体列的精度或标度约束。如果分配给此类列的值的小数点后位数超过指定的标度允许的范围，值将按该标度进行转换，即列的标度大于指定标度，可以按指定标度来进行转化（一般列允许的标度跟操作系统有关，和mysql版本）。

位类型

位类型，即BIT数据类型可用来保存位字段的值，BIT(M)类型表示允许存储M位数值，M范围为1到64，占用的空间为（M+7）/8字节。如果为BIT(M)列分配的值的长度小于M位，会在值得左边用0来填充，例如BIT(6)列分配一个值b’101’，其效果与分配b’000101’相同（这里b代表是BIT类型，位类型），要指定类型，可以使用b’value’符

CREATE TABLE t11(

a BIT(4)

)

INSERT INTO t11 SELECT b’1000’;

SELECT HEX(a),a FROM t11;

在这里插入图片描述 #### 字符类型

字符集

我们首先来认识一下字符集，字符集其实由字符组成的，通俗讲是字符集合嘛，比如什么什么支持gbk字符集，那么他就可以识别出gbk字符集里面包含的字符，然后来认识一下字符编码，字符编码其实是由字符集来支持的，字符编码其实是由字符和它对应的唯一二进制编码组成的，即形成一个映射关系，比如gbk字符集支持Unicode编码，那么gbk里面的字符就可以对应Unicode里面的映射关系，变成二进制，然后储存在计算机中，我们所说的乱码情况，就是字符集使用了不支持的编码方式，导致解码错误，也就是映射关系出现问题。

对于简体中文，我们习惯使用gbk或gb2312，这两者的关系是gbk是gb2312的超集，因此可以支持更多的汉字，不过当前mysql不支持中文字符集gb18030，因此在有些应用中已经出现gbk不能显示特定中文字体的情况了，如果使用繁体中文的话，big5是首选的字符集。

Unicode和utf8的区别

Unicode是一种在计算机上使用的字符编码，为每种语言的每个字符设定了统一且唯一的二进制编码，utf8是字符集。对于Unicode编码的字符集，尽量将所有CHAR字段设置为VARCHAR字段，因为对于CHAR字段，数据库会保存最大可能的字节数，比如CHAR(30)，字段中可以储存的字符长度为30，但表中使用的字符集允许一个字符占的最大字节为3，那么CHAR(30)，数据库可能存储90字节的数据。

对字符集的设置可以在Mysql的配置文件中完成(my.ini)

在这里插入图片描述 mysql可以细化每个对象字符集的设置

CREATE TABLE t12(

a VARCHAR(10) CHARSET gbk,

b VARCHAR(10) CHARSET latin1,

c VARCHAR(10)

)CHARSET=utf8;

c列这里没有设置字符集，所以会使用表的字符集utf8，如果表也没有字符集，那就会使用架构（数据库）时指定的字符集，如果在创建数据库中也没有规定字符集，那就会使用数据库配置文件中指定的字符集。

排序规则

排序规则是指对指定字符集下不同字符的比较规则，其特征有以下几点。

两个不同的字符集可能有相同的排序规则
每个字符集有一个默认的排序规则
常用的命令规则
_ci(case insensitive)表示大小写不敏感
_cs(case sensitive)表示大小写敏感
_bin(binary)表示二进制的比较

SHOW CHARSET; //可以查看Mysql支持的所有字符集，里面的Default_collation就是默认支持的排序规则

在这里插入图片描述

SHOW COLLATION; //查看支持的各种排序规则

在这里插入图片描述

SHOW COLLATION LIKE “gbk%”; //查找关于gbk字符集的排序规则

在这里插入图片描述如果需要查看gbk字符集默认的排序规则

SHOW CHARSET LIKE “gbk%”;

在这里插入图片描述

可以看到，gbk默认的比较方式是gbk_chinese_ci，跟上一章表Default字段为yes的对应。

排序规则有什么影响

CREATE TABLE t13(

a VARCHAR(10)

)CHARSET=utf8;

INSERT INTO t13 SELECT “a”;

INSERT INTO t13 SELECT “A”;

SELECT * FROM t13 WHERE a = “a”;

最后一条查询语句的结果是下图所示

在这里插入图片描述

再看看utf8的默认排序规则

SHOW CHARSET LIKE utf8

在这里插入图片描述可以看到utf8默认的排序规则是_ci，也就是大小写不敏感，所以对于语句where a = “a”，并不会区分大小写，将A也搜索了出来。

再查查

SELECT “a” = “A”; //查看当前会话的排序规则

在这里插入图片描述

可以看到返回值为1，所以认为这两个字符的比较结果是相等的，如果要进行区分大小写，可以设置当前会话的排序规则，语句为SET NAMES … COLLATE …

SET NAMES utf8 COLLATE utf8_bin //修改为二进制排序规则

在这里插入图片描述

此时，返回值就为0了，数据库认为a和A是不同的字符，但如果需要对表中字段进行区分，需要将指定列的排序规则进行修改

ALTER TABLE t13 MODIFY COLUMN a VARCHAR(10) COLLATE utf8_bin;

//再次执行

SELECT * FROM t13 WHERE a = ‘A’;

在这里插入图片描述

排序规则不仅会影响大小写的比较问题，也会影响着索引。

//将a列修改回原来定义的排序规则

ALTER TABLE t13 MODIFY COLUMN a VARCHAR(10) COLLATE utf8_general_ci;

//给a加一个唯一索引

ALTER TABLE t13 ADD UNIQUE KEY(a);

结果

在这里插入图片描述

因为大小写不敏感，所以存在着列a中是存在2个相同的值的，所以无法进行添加唯一索引。。索引是B+树，加索引时会进行比对。

CHAR和VARCHAR

这是两种最常用的字符串类型，一般来说CHAR(N)是用来保存固定长度的字符串，VARCHAR(N)用来保存可变长字符类型，对于CHAR类型，N的范围为0 ~ 255（ 2 8 2^8 28），对于VARCHAR类型，N的取值范围为0 ~ 65535（ 2 16 2^{16} 216），两种类型的N都代表字符长度，而不是字节长度。

注：不过在mysql4.1之前的版本，N是代表字节长度。

对于CHAR类型的字符串，Mysql数据库会自动对存储列的右边进行填充(Right Padded)操作，直到字符串达到指定的长度N，而在读取该列时，mysql数据库会自动将填充的字符删除（所以建议如果是固定长度的字符串才使用CHAR，不固定的使用VARCHAR）,有一种情况例外，可以将SQL_MODE设置为PAD_CHAR_TO_FULL_LENGTH。

ALTER TABLE t13 MODIFY COLUMN a CHAR(10);

SELECT a,HEX(a),LENGTH(a) FROM t13;

//修改Sql_mode模式

SET sql_mode=‘pad_char_to_full_length’;

SELECT a,HEX(a),LENGTH(a) FROM t13;

在这里插入图片描述

LENGTH()函数返回的是字节长度而不是字符长度（一个汉字是两个字节），CHAR_LENGTH()函数返回的是字节长度，因此可以得出CHAR类型会进行填满的（如果没有改变SQL_MODE,会将填充的进行删除，所以返回的不会是10，而是3），对于多字节字符集，CHAR(N)长度的列最多可占用的字节数为该字符集单字符最大的字节数 * 字符长度，例如在utf8下，CHAR(10)最多可能占用30个字节，因为utf8单个字符可以占最大3个字节。

SELECT @a:=‘MySql技术内幕’;

SELECT @a,HEX(@a),LENGTH(@a),CHAR_LENGTH(@a);

在这里插入图片描述

在utf8字符集下，一个汉字占3个字节，gbk下，一个汉字占2个字符。

CREATE TABLE t14(

a VARCHAR(10) CHARSET utf8,

b VARCHAR(10) CHARSET gbk

)

INSERT INTO t14 SELECT “MySql计数内幕”,“MySql计数内幕”;

SELECT a,LENGTH(a),CHAR_LENGTH(a),b,LENGTH(b),CHAR_LENGTH(b) FROM t14;

在这里插入图片描述

VARCHAR类型储存变长字段的字符类型，与CHAR类型不同的是，其存储时需要在浅醉长度列表上加上实际存储的字符，该字符会占用1~2字节的空间，具体的规则如下，当储存的字符串长度小于255（ 2 8 2^8 28）时，其需要1个字节空间，当大于255( 2 8 2^8 28)字节时，需要2字节的空间。所以对于lantin1字符集（单个字符占最大的字节为1）来说，CHAR(10)和VARCHAR(10)占用最大字节是不同的，CHAR(10)肯定是10个字节，但VARCHAR(10)至少为11个字节，如果为VARCHAR(255)甚至会12个字节，因为需要额外的字节来存放字符长度。

虽然CHAR和VARCHAR的存储方式不太相同，但是对于两个字符串的比较，都只比较其值，忽略CHAR值存在的右填充，即使将SQL_MODE设置为PAD_CHAR_TO_FULL_LENGTH也一样。

BINARY与VARBINARY

这两个与CHAR和VARCHAR比较类似，唯一不同的是BINARY与VARBINARY存储的是二进制的字符串（存储的是二进制，图片，视频那些），而且BINARY与VARBINARY没有字符集的概念，对其排序和比较都是按照二进制值进行对比的，

BINARY(N)和VARBINARY(N)中的N是指字节长度（CHAR,VARCHAR的N是字符长度）,对于BINARY(10),其可存储的字节固定为10，而对于CHAR(10),其存储的字符长度为10，但字节长度不一样（需要知道字符集单个字符所占用的最大字节）。

CREATE TABLE t15(

a BINARY(1)

)ENGINE=INNODB CHARSET=GBK;

//修改为gbk字符集

SET NAMES GBK;

//插入数据

INSERT INTO t15 SELECT ‘我’;

SELECT a,HEX(a) FROM t15;

结果

在这里插入图片描述

在表中，字段a是一个BINARY(1)占一个字节，而在GBK字符集中，一个汉字占两个字节，所以根本不够内存，所以在插入时会给出警告，提示字符被截断，如果SQL_MODE为严格模式，则会直接报错。

//修改a列为2字节

ALTER TABLE t15 MODIFY COLUMN a BINARY(2);

SELECT a,HEX(a) FROM t15;

在这里插入图片描述 CHAR VARCHAR和BINARY VARBINARY的不同之处

SELECT a,LENGTH(a) FROM t15;

在这里插入图片描述

首先第一个不同之处就是参数N，CHAR和VARCHAR的N是字符长度，而BINARY和VARBINARY的N是字节长度。第二个不同的是CHAR和VARCHAR进行比较时，比较的是存储的字符，忽略字符后的填充字符，而对于BINARY和VARBINARY来说，由于是按照二进制值来进行比较的（BINARY也会进行填充，而且比较时并不会忽略填充字符，由上图可知），因此结果会不同。

SELECT HEX(‘a’),HEX('a '),‘a’='a ';

在这里插入图片描述

SELECT HEX(BINARY(‘a’)),HEX(BINARY('a ')),BINARY(‘a’)=BINARY('a ');

在这里插入图片描述

BLOB和TEXT

BLOB是用来存储二进制大数据类型的（比如图片和视频），根据储存长度的不同，BLOB可细分为以下4中类型，括号中的数代表存储的字节数。

TINYBLOB( 2 8 2^8 28)
BLOB( 2 16 2^{16} 216)
MEDIUMBLOB( 2 24 2^{24} 224)
LONGBLOB( 2 32 2^{32} 232)

TEXT类型同BLOB一样，也可以细分为4种类型

TINYTEXT( 2 8 2^8 28)
TEXT( 2 16 2^{16} 216)
MEDIUMTEXT( 2 24 2^{24} 224)
LONGTEXT( 2 32 2^{32} 232)

在大多数情况下，可以将BLOB类型的列视为足够大的VARBINARY类型的列。同样，也可以将TEXT类型的列视为足够大的VARCHAR类型的列，然而，在BLOB和TEXT在以下几方面又不同于VARBINARY和VARCHAR

在BLOB和TEXT类型的列上创建索引时，必须指定索引前缀的长度，而VARCHAR和VARBINARY的前缀长度是可选的（索引前缀其实是适用于很长的数据，因为数据很长，会让索引变得也很长（索引要进行比对的），所以要规定前缀索引来索引开始的一部分字符，如果前面一部分字符相同，那就是匹配成功，这样可以大大节约索引空间，从而提高索引效率，但这样会降低索引的选择性即可能存在匹配错误情况，前面一部分匹配成功，但后面不正确）
BLOB和TEXT类型的列不能有默认值
在排序时，只是用列的前max_sort_length个字节（默认值为1024，该参数是一个动态参数，可以在客户端进行更改）

在数据库中，最小的存储单位是页（也可以称为块）。为了有效存储列类型为BLOB或者TEXT的大数据类型，一般将这种属性的列的值存放在行溢出页中，而数据页存储的行数据只包含BLOB或TEXT类型数据列的前一部分数据。

数据页是由许多的行数据组成，每行数组由各个列组成，对于列类型为BLOB的数据，InnoDB存储引擎只存储前20字节，而该列的完整数据则存放在BLOB的行溢出页中，在这种方式下，数据页中能存放大量的行数据，从而提高了数据的查询效率

此外，InnoDB引擎会将大VARCHAR类型字符串，比如VARCHAR(65530),也就是VARCHAR最大字符长度( 2 16 2^{16} 216)，会自动转化为TEXT或者BLOB类型。

在这里插入图片描述