一、存储引擎
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) | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 非常小整数值 |
SMALLINT | 2 bytes | (-32 768,32 767) | (0,65 535) | 小整数值 |
MEDIUMINT | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 中整数值 |
INT或INTEGER | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 正常大小整数值 |
BIGINT | 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 大整数值 |
FLOAT | 4 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) | 单精度浮点小数 |
DOUBLE | 8 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) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | -838:59:59'/'838:59:59 | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038,结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD 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)