数据库 MySQL
1 存储引擎
1.1 简介
需要使用不同的处理机制去存储管理不同类型的数据。
存储引擎是指不同的处理机制。
1.2 MySQL主要存储引擎
- InnoDB
MySQL 5.5及其之后的默认存储引擎；
支持事务、行锁和外键，InnoDB存储数据比MyISAM更安全； - MyISAM
MySQL 5.5之前的默认存储引擎；
MyISAM在查询 速度上比InnoDB更快； - MEMORY
内存引擎，数据全部存放于服务端内存中；
速度快，但断电后/服务端重启后数据丢失，可用于临时数据存储； - BLACKHOLE
无论存什么，都会立即消失，可用于数据库集群部署。
show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
2.3 选择存储引擎创建表
create table t1(id int) engine=innodb;
create table t12(id int) engine=myisam;
create table t13(id int) engine=memory;
create table t14(id int) engine=blackhole;
# InnoDB
t11.frm # .frm 存储表结构
t11.ibd # .ibd 存储表数据
# MyISAM
t12.frm
t12.MYD # .MYD 存储表数据
t12.MYI # .MYI 存储表索引 (index)
# MEMORY 只有表结构，数据只存放于服务端内存中。
t13.frm
# BLACKHOLE 只有表结构，不会存储数据。
t14.frm
2 创建表的语法
2.1 创建表的完整语法
create table 表名(
字段名1 类型(宽度) 约束条件1 约束条件2，
字段名2 类型(宽度) 约束条件2，
字段名3 类型(宽度) 约束条件3
);
注意：
- 同一张表中字段名不能重复；
- 宽度和约束条件是可选的，字段名和类型是必须的，约束条件可以写多个；
- 最后一行不能加逗号。
2.2 宽度与严格模式
2.2.1 宽度
宽度一般是对存储数据的限制，整形是特例。
create table t1(name char); # 默认宽度是1个字符
对于不同版本，对插入宽度大于规定宽度的数据时会进行不同的操作。
- MySQL 5.6及以下
默认没有开启严格模式，如果插入数据的宽度大于规定宽度，MySQL会对其进行截取； - MySQL 5.7及以上
默认开启严格模式，如果插入数据的宽度大于规定宽度，MySQL会立即报错。
推荐开启严格模式
未开启严格模式，数据库会对超出规定长度的数据进行截取操作，这会增加数据库的操作量。
使用数据库准则：尽量减少数据库的操作量，不要给数据库增加额外的不必要的操作压力，数据库如果因压力过大而崩溃，损失惨重。
2.2.2 严格模式
查看是否开启严格模式
show variables like "%mode";
+----------------------------+------------------------+
| Variable_name | Value |
+----------------------------+------------------------+
| binlogging_impossible_mode | IGNORE_ERROR |
| block_encryption_mode | aes-128-ecb |
| gtid_mode | OFF |
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| pseudo_slave_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | NO_ENGINE_SUBSTITUTION |
+----------------------------+------------------------+
可以看出，MySQL 5.6的sql_mode默认为NO_ENGINE_SUBSTITUTION，即默认未开启严格模式。
备注：模糊查询
SQL语句条件关键字为like
%：可以匹配任意数量的字符；
_：只能匹配任意一个字符。
修改严格模式
# 临时修改，只在当前窗口(session 会话)有效
set session sql_mode='STRICT_TRANS_TABLES';
# 全局有效
set global sql_mode='STRICT_TRANS_TABLES';
- 修改后需要重启客户端；
- 无论临时修改还是全局修改都没有影响配置文件，服务端重启后修改会复原。
2.3 约束条件
宽度和约束条件关系
宽度用于限制数据的存储，约束条件是在宽度的基础上增加额外的限制条件。
约束条件 null / not null
create table t2(id int, name char not null);
insert into t2 values(1, '');
# Query OK, 1 row affected (0.01 sec)
insert into t2 values(2, 'A'), (3, null);
# Query OK, 2 rows affected, 1 warning (0.01 sec)
# Records: 2 Duplicates: 0 Warnings: 1
insert into t2 values(4, null);
# ERROR 1048 (23000): Column 'name' cannot be null
3 数据类型
2.1 整形
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| tinyint | 1字节 | [-128 , 127] | [0, 255] | 小整数值 |
| smallint | 2字节 | [-32,768(-2^15) , 32,767(2^15 - 1)] | [0, 65535] | 大整数值 |
| mediumint | 3字节 | [-8388608 , 8388607] | [0, 16777215] | 大整数值 |
| int / integer | 4字节 | [-2,147,483,648, 2,147,483,647] | [0, 4294967295] | 大整数值 |
| bigint | 8字节 | [-9223372036854775808, 9223372036854775807] | [0, 18446744073709551615] | 极大整数值 |
注意：
- 默认有符号；
- 如果超出数据类型的范围，只保留最大可接受值（即数据类型对应的边界值）;
- 整形小括号内的宽度不是限制的位数，而是显示长度。
如果数据宽度未超过限制宽度，用空格填充；
如果数据宽度超过限制宽度，有几位就存几位，但必须处于数据类型的范围内。
总结：
针对整形数据，括号内一般无需指定宽度，因为每个数据类型的默认宽度已经足够显示该数据类型对应范围内的数值了。
create table t3(id int(8));
insert into t3 values(123456789);
select id from t3;
+-----------+
| id |
+-----------+
| 123456789 |
+-----------+
约束条件 unsigned
create table t1(id tinyint unsigned);
insert into t1 values(-5);
select id from t1; # 0
约束条件 zerofill
create table t2(id int(8) unsigned zerofill);
insert into t2 values(123), (-5), (1234567890);
select id from t2;
+------------+
| id |
+------------+
| 00000123 |
| 00000000 |
| 1234567890 |
+------------+
2.2 浮点型
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| float | 4字节 | [-3.402823466E+38, -1.175494351E-38], 0, [1.175494351E-38, 3.402823466E+38] | 0, [1.175494351E-38, 3.402823466E+38] | 单精度浮点值 |
| double | 8字节 | [-1.7976931348623157E+308, -2.2250738585072014E-308], 0, [2.2250738585072014E-308, 1.7976931348623157E+308] | 0, [2.2250738585072014E-308, 1.7976931348623157E+308] | 双精度浮点值 |
| decimal | 对于decimal(M, D)，如果Ｍ＞Ｄ，大小为Ｍ＋２，否则为Ｄ＋２ | 依赖于Ｍ和D的值 | 依赖于Ｍ和D的值 | 小数 |
存储限制
float(255, 30) # 数据一共255位，小数部分占30位。
double(255, 30)
decimal(65, 30)
精确度
float < double < decimal
decimal可以认为是精确度无损的。
2.3 字符类型
char 定长 存取速度较块
varchar 变长 节省存储空间
char(4) # 超过4个字符直接报错，不够时用空格补充，MySQL在显示时会自动将多余空格去除。
varchar(4) # 超过4个字符直接报错，不够时有几个存几个。
禁止自动去除空格
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
create table t1(name char(4));
create table t2(name varchar(4));
insert into t1 values('a');
insert into t2 values('a');
select char_length(name) from t1; # 4
select char_length(name) from t2; # 1
总结：
- char的缺点是浪费存储空间，但存取过程较简单，直接存储固定的字符即可。
- varchar的优点是节省存储空间，但存储数据时会在数据前制作1Bytes的报头来记录所存储的数据长度，读取数据时需要先读取报头数据，因此存取数据速度较慢。
现在推荐使用varchar。
2.4 时间类型
date：年 月 日
datetime：年 月 日 时 分 秒
time：时 分 秒
year：年
2.5 枚举与集合类型
提前列举出选项供用户选择。
- 枚举：enum
多选一
create table t1(
gender enum('male', 'female', 'others')
);
- 集合：set
多选多
create table t1(
hobby set('reading', 'sports', 'others')
);
insert into t1 values('reading, others');
插入数据时，如果数值不在枚举/集合提供的选项范围中，立即报错。
2309
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
