46、数据类型

数据库编程 / 2021-01-19

一、存储引擎

1.1、什么是存储引擎?

存储数据的文件有很多种类型,针对不同的数据类型需要用不同的处理机制来处理,比如说Windows上,图片处理用PNG格式、表格用Excel等等!

数据库的表也有不同的类型,表的类型不同,对应的MySQL处理机制也不同,表类型又称存储引擎!

1.2、存储引擎有哪些

列举几个MySQL较为常见的存储引擎

InnoDB

MySQL5.5版本后默认的存储引擎,也是MySQL最常用的存储引擎,没有之一!支持事物、行锁、外键、加密表等!

MyISAM

是5.5版本以前的默认引擎,老东西了,不过速度比InnoDB快些,因为数据不够安全!

MEMORY

内存引擎,你懂的,断电数据什么的就丢失!

BLACKHOLE

黑洞,就像Linux的/dev/null!不过我这个MariaDB 10.3.27已经没有这个引擎了!

MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.003 sec)

1.3、如何指定引擎?

创建表时指定引擎
MariaDB [study]> CREATE TABLE student(id int,name char(4),age int) ENGINE=innodb;
Query OK, 0 rows affected (0.030 sec)
配置文件中指定默认引擎
[mysqld]
default-storage-engine=INNODB
innodb_file_per_table=1

二、严格模式

完整的创建表语句(伪代码):CREATE TABLE 表名(字段1 类型[(宽度) 约束条件],字段2 类型[(宽度) 约束条件]),约束条件可以是多个!

2.1、什么是宽度?

CREATE TABLE student(id int,name char(4),age int) ENGINE=innodb;,char括号里的4就是宽度,代表最大接受4个字符!

2.2、什么是约束条件?

顾名思义,是给数据存储设置条件的,达不到就不给存!

MySQL默认数据是可以为空的,比如下面我插入数据时故意不写name的值也是可以的!

MariaDB [study]> INSERT INTO student VALUES(1,'',18);
Query OK, 1 row affected (0.004 sec)

MariaDB [study]> SELECT * FROM student;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 |      |   18 |
+------+------+------+
1 row in set (0.000 sec)

当设置了约束条件后呢?什么情况!为什么设置了NOT NULL还可以插入空值!!!

MariaDB [study]> CREATE TABLE test(id int,name char(4) NOT NULL,age int);
Query OK, 0 rows affected (0.023 sec)

MariaDB [study]> INSERT INTO test VALUES(1,'',18);
Query OK, 1 row affected (0.012 sec)

MariaDB [study]> SELECT * FROM test;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 |      |   18 |
+------+------+------+
1 row in set (0.000 sec)
2.2.1、NULL与空值的区别

这里需要说明一下,NOT NULL != 空值!怎么解释呢?

在MySQL中,空值是不占用空间的,而NULL是占用空间的!还是举个例子吧,我拿一个玻璃杯,空值代表玻璃杯抽成了真空,NULL代表玻璃杯充满了空气,虽然看起来都是空的,实际却不一样,空气也是有质量的!所以,NOT NULL指的是不可以写入NULL!

MariaDB [study]> INSERT INTO test VALUES(1,NULL,18);
ERROR 1048 (23000): Column 'name' cannot be null

2.3、宽度和约束条件什么关系?

宽度是用来限制数据的存储,约束条件是在宽度的基础上增加的额外约束条件,正所谓难上加难!

2.4、什么是严格模式?

当我们给数据设置了宽度限制时,则存储数据时则必须严格按照规则来存,否则报错!MySQL5.7及之后的版本默认都是开启严格模式,前面的老版本默认不开启!不开启严格模式的话,比如设置了char(1),你存入'sanxi'五个字符,只会接受一个字符!

MariaDB [study]> INSERT INTO student VALUES(1,'sanxi666',18);  # 因为上面限制了name最多接受4个字符
ERROR 1406 (22001): Data too long for column 'name' at row 1
查看严格模式是否开启
MariaDB [study]> SHOW VARIABLES LIKE "%sql_mode";
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

SHOW VARIABLES LIKE "%sql_mode";模糊查询

%:匹配任意多个字符

_:匹配任意单个字符

修改严格模式
当前窗口生效
SET SESSION sql_mode='STRICT_TRANS_TABLES';  # 开启严格模式,仅当前窗口有效
全局生效
SET GLOBAL sql_mode='STRICT_TRANS_TABLES';  # 开启严格模式,全局生效,改完需要退出重登!

三、数据类型

上面说过存储引擎即表的类型,不同表存放的数据可以是不同类型,每种数据类型都有着独特的宽度,但宽度是可以指定的!MySQL的数据类型跟Python数据类型还是比较接近的!

3.1、数值类型

数值类型分整型和浮点型,来张表瞅瞅!其实表里面有符号就是有负数,无符号代表正数!

类型(Types)大小范围(有符号)范围(无符号)用途
TINYINT1 byte(-128,127)(0,255)非常小整数值
SMALLINT2 bytes(-32 768,32 767)(0,65 535)小整数值
MEDIUMINT3 bytes(-8 388 608,8 388 607)(0,16 777 215)中整数值
INT或INTEGER4 bytes(-2 147 483 648,2 147 483 647)(0,4 294 967 295)正常大小整数值
BIGINT8 bytes(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)大整数值
FLOAT4 bytes(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)0,(1.175 494 351 E-38,3.402 823 466 E+38)单精度浮点小数
DOUBLE8 bytes(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)双精度浮点小数
DECIMAL对DECIMAL(M,D)如果M>D,为M+2否则为D+2依赖于M和D的值,M最大为255,D最大为30依赖于M和D的值定点小数
3.1.1、整型演示

整型数值可以存储年龄、等级、ID、号码等等!上面说了有无符号的区别,其实MySQL是默认带符号,但是年龄这些数据不可能是负数的!

TINYINT演示
MariaDB [study]> INSERT INTO tiny VALUES(-129);  # tinyint带符号时最小值为-128,因为5.7以后默认开启严格模式,所以报错了!
ERROR 1264 (22003): Out of range value for column 'age' at row 1

可添加约束条件unsigned,取消带符号,强制指定为正数!

MariaDB [study]> INSERT INTO tiny VALUES(-5);
ERROR 1264 (22003): Out of range value for column 'age' at row 1
3.1.2、注意事项

整型括号里面的数字不是表示限制位数,仅仅是限制显示结果而已,与存储范围无关!整型的存储范围即表格中所示,是固定死的!所以没必要为整型指定宽度,默认就得了!

MariaDB [study]> CREATE TABLE ha(age int(11) unsigned);
Query OK, 0 rows affected (0.033 sec)

MariaDB [study]> INSERT INTO ha VALUES(18316610160);
ERROR 1264 (22003): Out of range value for column 'age' at row 1
3.1.3、浮点型

浮点型数值可以用于像身高、工资、体重等等!

FLOAT
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

单精度浮点数(非准确小数值),M是数字总个数,D是小数点后个数。m最大值为255,d最大值为30!随着小数的增多,精度变得不准确!

DOUBLE
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30!随着小数的增多,精度比float要高,但也会变得不准确!

DECIMAL
DECIMAL[(M,D)] [UNSIGNED] [ZEROFILL]

准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。随着小数的增多,精度始终准确!

精确度

float < double < decimal

3.2、字符类型

这里只说常用的char和varchar!char和varchar括号内的参数指的都是字符的个数,如char(8)指的是8个字符!

3.2.1、char

定长字符串,默认定长宽度为4个字符!字符长度范围:0-255,不够4个空格来补,多了报错!

3.2.2、varchar

变长字符串,默认定长宽度为4个字符!超过4个字符报错,不够4个则有几个存几个!不会跟上面一样用空格补全!字符长度范围:0-65535,如果是中文且长度大于21845(UTF8一个中文字符对应3个字节)会提示用其他类型,因为MySQL行的最大限制为65535字节!

3.2.33、char、varchar对比

SELECT检索时char很不要脸地将自己补全的空格给自动剔除了,装得好像自己没浪费过空间一样;而varchar很老实,存了多少,就显示多少!

演示
MariaDB [study]> CREATE TABLE test(ch char(5),var varchar(5));
Query OK, 0 rows affected (0.053 sec)

MariaDB [study]> INSERT INTO test VALUES('带带我','带带我');
Query OK, 1 row affected (0.015 sec)

MariaDB [study]> SELECT ch,char_length(ch),var,char_length(var);
ERROR 1054 (42S22): Unknown column 'ch' in 'field list'
MariaDB [study]> SELECT ch,char_length(ch),var,char_length(var) FROM test;  # char_length()统计字符个数
+--------+-----------------+--------+------------------+
| ch     | char_length(ch) | var    | char_length(var) |
+--------+-----------------+--------+------------------+
| 带带我 |               3 | 带带我 |                3 |
+--------+-----------------+--------+------------------+
1 row in set (0.003 sec)

看起来好像都一样哦!哼!来人!上照妖镜!

MariaDB [study]> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';  # 关闭显示结果时自动剔除空格
Query OK, 0 rows affected (0.001 sec)

MariaDB [study]> SELECT ch,char_length(ch),var,char_length(var) FROM test;  # char原形毕露了,3个中文字符+2个空格
+----------+-----------------+--------+------------------+
| ch       | char_length(ch) | var    | char_length(var) |
+----------+-----------------+--------+------------------+
| 带带我   |               5 | 带带我 |                3 |
+----------+-----------------+--------+------------------+
1 row in set (0.000 sec)
总结

InnoDB存储引擎:建议使用VARCHAR类型

单从数据类型的实现机制去考虑,char数据类型的处理速度更快,有时甚至可以超出varchar处理速度的50%。但对于InnoDB数据表,内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),因此在本质上,使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而,主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR,因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

补充

一般工作环境不用考虑字符类型和字段名,因为产品经理给你发的邮件上已经写明了!

3.3、时间类型

类型大小(bytes)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3-838:59:59'/'838:59:59HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038,结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值,时间戳
注意事项

单独插入时间时,需要以字符串的形式,按照对应的格式插入!

3.4、枚举与集合类型

枚举(enum)

多选一

MariaDB [study]> CREATE TABLE shirts(name varchar(8),size ENUM('M','L','XL'));
Query OK, 0 rows affected (0.032 sec)

MariaDB [study]> INSERT INTO shirts VALUES('T-shirts','L');
Query OK, 1 row affected (0.015 sec)

MariaDB [study]> SELECT name,size FROM shirts;
+----------+------+
| name     | size |
+----------+------+
| T-shirts | L    |
+----------+------+
1 row in set (0.000 sec)

不在范围内则为空!

MariaDB [study]> INSERT INTO shirts VALUES('T-shirts','H');
Query OK, 1 row affected, 1 warning (0.004 sec)

MariaDB [study]> SELECT name,size FROM shirts;
+----------+------+
| name     | size |
+----------+------+
| T-shirts | L    |
| T-shirts |      |
+----------+------+
2 rows in set (0.000 sec)

集合(set)

多选多,插入数据时多个值需要在引号内用逗号隔开

MariaDB [study]> CREATE TABLE student(name varchar(16),hobby SET('playing','reading','running'));
Query OK, 0 rows affected (0.034 sec)

MariaDB [study]> INSERT INTO student VALUES('sanxi','playing,running');
Query OK, 1 row affected (0.017 sec)

MariaDB [study]> SELECT name,hobby FROM student;
+-------+-----------------+
| name  | hobby           |
+-------+-----------------+
| sanxi | playing,running |
+-------+-----------------+
1 row in set (0.000 sec)

不在范围内则为空!

MariaDB [study]> INSERT INTO student VALUES('sanxi','playing,zhuangbi');
Query OK, 1 row affected, 1 warning (0.003 sec)

MariaDB [study]> SELECT name,hobby FROM student;
+-------+-----------------+
| name  | hobby           |
+-------+-----------------+
| sanxi | playing,running |
| sanxi | playing         |
+-------+-----------------+
2 rows in set (0.000 sec)
世间微尘里 独爱茶酒中