MySQL 笔记

数据库

简介

MySQL（官方发音为/maɪ ˌɛskjuːˈɛl/“My S-Q-L”[1]，但也经常读作/maɪ ˈsiːkwəl/“My Sequel”）原本是一个开放源代码的关系数据库管理系统，原开发者为瑞典的MySQL AB公司，该公司于2008年被昇阳微系统（Sun Microsystems）收购。2009年，甲骨文公司（Oracle）收购昇阳微系统公司，MySQL成为Oracle旗下产品。

MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟，它也逐渐用于更多大规模网站和应用，比如维基百科、Google和Facebook等网站。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

但被甲骨文公司收购后，Oracle大幅调涨MySQL商业版的售价，且甲骨文公司不再支持另一个自由软件项目OpenSolaris的发展，因此导致自由软件社区们对于Oracle是否还会持续支持MySQL社区版（MySQL之中唯一的免费版本）有所隐忧，MySQL的创始人麦克尔·维德纽斯以MySQL为基础，成立分支计划MariaDB。而原先一些使用MySQL的开源软件逐渐转向MariaDB或其它的数据库。例如维基百科已于2013年正式宣布将从MySQL迁移到MariaDB数据库[2]。

大部分 Linux 发行版已经使用 MariaDB 作为 MySQL 的官方实现，包括 ArchLinux。MariaDB 基本与 MySQL 兼容，用户基本可以无障碍的迁移到 MariaDB，本篇笔记安装的也是 MariaDB。

MySQL 是 C/S 结构的，服务端是 mysqld 守护进程，客户端是 mysql 命令行交互环境。使用 mysql 连接数据库之前，应该先在目标主机上运行 mysqld 服务。mysqld 默认监听 3306/tcp 端口，同时 mysqld 还会创建 /run/mysqld/mysqld.sock Unix 套接字文件，本机连接建议使用 UNIX 套接字（localhost 而非 127.0.0.1），因为没有了多余的 TCP/IP 协议开销，性能更好。

思考：为什么需要数据库？
使用文件不也可以存储数据吗，为什么还要弄出数据库这种东西呢？因为文件系统提供的 API 太低级，open/read/write/close，除此之外，文件系统不会帮你做任何事情，如果你需要查找文件中个某个信息，你必须自己编程，或者使用 grep 等 unix 工具，如果要修改，需通过 sed 等工具，总之，仅仅通过文件系统的 API 来进行数据的增删改查太麻烦，而且效率很低，同时会让软件开发人员的负担变重，因为不仅要处理数据的逻辑，还要关心数据的具体存储问题，想想头都大。

而数据库则提供了相对比较高级的 API 让我们专门关心于数据的逻辑处理，具体底层怎么操作、怎么存储的，我们无需关心。比如查询数据 SELECT、插入数据 INSERT，为了与数据库进行交互，大部分数据库都采用了 SQL（结构化查询语言）API，虽然不同的数据库系统的 SQL 语句不太相同，但是基本语法还是通用的，如果程序要与数据库交互，那就必须使用 SQL 语言。

SQL 结构化查询语言
SQL（聆听i/ˈɛs kjuː ˈɛl/,[4] or 聆听i/ˈsiːkwəl/;[5]结构化查询语言[6][7][8][9]）是一种特定目的程序语言，用于管理关系数据库管理系统（RDBMS），或在关系流数据管理系统（RDSMS）中进行流处理。

SQL基于关系代数和元组关系演算，包括一个数据定义语言和数据操纵语言。SQL的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。尽管SQL经常被描述为，而且很大程度上是一种声明式编程（4GL），但是其也含有过程式编程的元素。

SQL是对埃德加·科德的关系模型的第一个商业化语言实现，这一模型在其1970年的一篇具有影响力的论文《一个对于大型共享型数据库的关系模型》[10]中被描述。尽管SQL并非完全按照科德的关系模型设计，但其依然成为最为广泛运用的数据库语言。 [11][12]

SQL在1986年成为美国国家标准学会（ANSI）的一项标准，在1987年成为国际标准化组织（ISO）标准。[13]在此之后，这一标准经过了一系列的增订，加入了大量新特性。虽然有这一标准的存在，但大部分的SQL代码在不同的数据库系统中并不具有完全的跨平台性。

SQL是高级的非过程化编程语言，它允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解其具体的数据存放方式。而它的界面，能使具有底层结构完全不同的数据库系统和不同数据库之间，使用相同的SQL作为数据的输入与管理。它以记录项目〔records〕的合集（set）〔项集，record set〕作为操纵对象，所有SQL语句接受项集作为输入，回提交的项集作为输出，这种项集特性允许一条SQL语句的输出作为另一条SQL语句的输入，所以SQL语句可以嵌套，这使它拥有极大的灵活性和强大的功能。在多数情况下，在其他编程语言中需要用一大段程序才可实践的一个单独事件，而其在SQL上只需要一个语句就可以被表达出来。这也意味着用SQL可以写出非常复杂的语句，在不特别考虑性能下。

SQL 同时也是数据库文件格式的扩展名。习惯上，SQL 语句的关键字为大写形式，当然，大部分数据库管理系统都对关键字的大小写不敏感，我个人的话比较喜欢使用小写形式，因为绝大多数编程语言都是使用小写字母作为关键字，当然，正式场合，比如面试时，建议使用大写，不然可能留下你对 SQL 不熟悉的不好印象哦（千万别高估了 HR 的智商，它们有时候挺智障的）。

对于 Java 来说，用的最多的关系型数据库管理系统是 Oracle、MySQL。Oracle 是大型数据库系统，安装就够你折腾一天了，新手的话，建议先学习 MySQL，然后学习 Oracle，由易到难，其实 SQL 语句都差不多，只不过有细微差异而已。我也是打算先学习 MySQL。

SQL 结构化查询语言和 C 家族编程语言一样，使用分号表示一个语句的结束。

关系型数据库管理系统 RDBMS
代表：Oracle、DB2、MSSQL、MySQL

非关系型数据库 NoSQL
代表：redis、mongodb、memcached

目前主流还是 RDBMS，NoSQL 主要是用来做临时数据库用的，比如高速缓存。

安装

# mariadb           -- server
# mariadb-clients   -- client
pacman -S mariadb mariadb-clients
# python 开发的语法高亮、自动补全 mysql 客户端
pip install mycli
# 初始化 mysqld
mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
# 启动 mysqld.service
systemctl start mysqld.service
# 进行一些安全措施优化
mysql_secure_installation
systemctl restart mysqld.service

添加用户
默认只有 root 用户（mysql 用户与 linux 用户没有关系），root 用户即管理员用户，它拥有至高无上的权利，因此实际操作中非常不建议使用 root 用户，应该使用普通用户，这是添加方法：

$ mysql -uroot -p
MariaDB> # create
MariaDB> CREATE USER 'zfl9'@'localhost' IDENTIFIED BY '123456';
MariaDB> # grant priv
MariaDB> GRANT ALL PRIVILEGES ON mydatabase.* TO 'zfl9'@'localhost';
MariaDB> # flush priv
MariaDB> FLUSH PRIVILEGES;
MariaDB> # quit client
MariaDB> quit

配置文件
mysql 配置文件分为系统范围、用户范围，使用 mysqld --help --verbose | more 可查看：

Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf 
# /etc/mysql/my.cnf 系统范围
# ~/.my.cnf         用户范围

远程连接
通常不建议这么做，如果需要远程连接，请使用 SSH、VNC、VPN，修改 /etc/mysql/my.cnf：

[mysqld]
#skip-networking
bind-address = 192.168.1.1

如果需要禁止远程连接，请注释 bind-address，然后取消注释 skip-networking。然后重启 mysqld.service，禁用 networking 后，你仍然可以从 localhost 连接（使用 unix 套接字）。

自动补全
默认情况下，mysql 交互式客户端不会进行自动补全（Tab），如果需要像 shell 那样利用 Tab 键自动补全，修改 /etc/mysql/my.cnf，将 no-auto-rehash 替换为 auto-rehash，重启客户端。

字符编码
强烈建议使用 utf8mb4，而不是 utf8，因为 mysql 的 utf8 只支持 BMP 基本平面的 Unicode 字符，也就是 65536 个字符，它实际上是阉割版的 utf8（这应该是历史遗留问题，可能 mysql 推出 utf8 编码时只有基本平面字符），修改 /etc/mysql/my.cnf，添加以下配置，然后重启 mysqld：

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
#collation_server = utf8mb4_unicode_ci

增加字符限制
不是很清楚，个人感觉应该是增加索引的 767/3072 字节长度限制的，配置：

[mysqld]
innodb_large_prefix = 1
innodb_file_per_table = 1
innodb_file_format = barracuda

systemctl restart mysqld.service

建表时指定 ROW_FORMAT=DYNAMIC 表选项即可：

mysql> create table if not exists products (
   ->   day date not null,
   ->   product_id int not null,
   ->   dimension1 varchar(500) not null,
   ->   dimension2 varchar(500) not null,
   ->   unique index unique_index (day, product_id, dimension1, dimension2)
   -> ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; # 关键在这里，COMPRESSED 也可以。
Query OK, 0 rows affected (0.02 sec)
# 默认情况下，索引的单列长度最大为 767 byte，索引的总长度最大长度为 3072 byte
# products 的索引的单列长度最大为 3072 byte，索引的总长度最大依旧为 3072 byte

bin-log 配置
默认情况下，mysqld 会在 /var/lib/mysql 中创建二进制日志文件。这对复制主服务器或数据恢复很有用。但是这些二进制日志会占用你的磁盘空间。如果您不打算使用复制或数据恢复功能，可以通过在 /etc/mysql/my.cnf 中注释掉这些行来禁用二进制日志记录：

#log-bin=mysql-bin
#binlog_format=mixed`

timezone 配置

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p mysql

mysqld 升级
升级数据库后建议运行 mysql_upgrade 命令来让旧的数据库适应新的 mysqld 版本：

mysql_upgrade -uroot -p

mysqlcheck 工具

# 检查所有数据库中的所有表：
mysqlcheck --all-databases -uroot -p -c
# 分析所有数据库中的所有表：
mysqlcheck --all-databases -uroot -p -a
# 修复所有数据库中的所有表：
mysqlcheck --all-databases -uroot -p -r
# 优化所有数据库中的所有表：
mysqlcheck --all-databases -uroot -p -o

重置 root 密码

# step1
systemctl stop mysqld.service
mysqld_safe --skip-grant-tables &
# step2
$ mysql -uroot mysql
mysql> use mysql;
mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit
# step3
systemctl start mysqld.service

入门

基本概念
DBMS（数据库管理系统）由多个 database（数据库）组成，每个 database 一般都代表一个独立的项目，就像一个域名一样。而每个 database 又由多个 table（数据表）组成，table 是一个二维表（行和列，由表头和数据组成），table 的每一行（row）都是一个记录（record），每一列（column）都是一个字段（field）。行/列是从结构角度出发的，记录/字段是从数据角度出发的。数据表（二维表）一般的形式为（第一行为表头，每列为不同的字段，每行都是一份数据）：

底层存储
MySQL 中的每个 database 都对应一个同名文件夹，database 中的每个 table 都对应两个同名文件（后缀不同）。所以，库名和表名的大小写是否敏感依赖于底层操作系统（准确的说是文件系统），所以在 unix 中，库名和表名都是严格区分大小写的，但在 windows 中，库名和表名是不区分大小写的。注意，字段名无论是什么操作系统，都是不区分大小写的，变量名在 unix 中是区分大小写的，但在 windows 中没有什么是区分大小写的，全部都是大小写不敏感的（真是一个神奇的操作系统）。

总结，mysql 其实就是字段名 column name 不区分大小写，其它的都区分，但是 windows 这个奇葩的系统除外，它什么都不区分大小写。

命名规范
库名、表名、列名 一律使用小写字符，单词之间使用下划线分隔，以消除大小写问题带来的疑惑。

用户管理
mysql 中默认只有 root 用户，root 用户没有任何权限限制，因为它就是管理员，显然，生产环境中是不会使用 root 用户操作的，唯一需要使用 root 用户的可能就是 DBA（数据库管理员）了。一般，DBA 会创建一个普通用户，然后限制它只能使用某个数据库，只能进行什么操作（权限，在 MySQL 中也称为特权），开发人员只需要使用 DBA 给我们的帐号和密码就行，其它不用操心。

mysql 的用户信息存放在 mysql.user 表中，创建用户可以直接往此表中增加一个记录，也可以使用 mysql 提供的创建用户指令，我更推荐使用后者，因为 mysql.user 表的字段名可能会更改。

连接数据库
mysql -h<host> -P<port> -u<user> -p[pass] [database]

• -h<host>：服务器地址，如 -h192.168.1.1 默认 localhost
• -P<port>：服务器端口，如 -P6666 默认 3306
• -u<user>：mysql 账户，如 -uroot 默认 root
• -p[pass]：mysql 密码，如 -p123456 或 -p（交互验证）
• [database]：进入的数据库，如 mysql -p mysql 进入 mysql 数据库

退出交互式命令行请使用 exit、quit 命令。

mysql 中的 127.0.0.1 与 localhost 是不一样的，127.0.0.1 表示使用 TCP/IP 协议连接，而 localhost 表示使用 Unix Socket 连接，很明显 localhost 更好，使用 status 命令可查看。

如果想直接在命令行中运行 mysql 语句，可以使用 -e 选项，如 mysql -uroot -p -e'show databases;'，与 perl 一样，最后一个 sql 语句中的分号可以省略。如果想要执行一个 sql 文件，那么可以使用 stdin 重定向的方式进行（仅适用于 unix 系统）：mysql -uroot -p </path/to/data.sql，或者管道 command | mysql -uroot -p。

数据库基本操作
基本操作可以用四个字概括：增删改查，英文 Create, Retrieve, Update and Delete (CRUD)。
根据操作对象的不同，我们可以将 SQL 基本操作粗略的分为三类：库操作、表操作、数据操作。

MariaDB [test]> status;
--------------
mysql  Ver 15.1 Distrib 10.1.35-MariaDB, for Linux (x86_64) using readline 5.1
Connection id:      5
Current database:   test
Current user:       root@localhost
SSL:            Not in use
Current pager:      less
Using outfile:      ''
Using delimiter:    ;
Server:         MariaDB
Server version:     10.1.35-MariaDB MariaDB Server
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /run/mysqld/mysqld.sock
Uptime:         13 hours 37 min 45 sec
Threads: 2  Questions: 69  Slow queries: 0  Opens: 30  Flush tables: 1  Open tables: 21  Queries per second avg: 0.001
--------------
MariaDB [test]> select version();
+-----------------+
| version()       |
+-----------------+
| 10.1.35-MariaDB |
+-----------------+
1 row in set (0.00 sec)
MariaDB [test]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)
MariaDB [test]> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

大小写问题
mysql 中，database、table 名对大小写敏感（windows 除外），field 名对大小写不敏感。
虽然 field 字段名对大小写不敏感，但是习惯上我们会以首字母大写的形式描述 field 字段名。

SQL 引号问题
SQL 标准使用单引号包含字符串，如果字符串中包含单引号，那么需要使用两个连续单引号来表示单引号本身（和 printf 的 %% 表示 % 类似），双引号是 MySQL 语法，Oracle 只能用单引号：

'www.zfl9.com'
'my name is ''Otokaze''' # 使用两个单引号表示单引号本身

单引号、双引号、反引号

• 单引号：标准 SQL 中字符串使用单引号，表示单引号本身需使用两个单引号；
• 双引号：MySQL 对标准 SQL 的扩展，允许使用单引号、双引号来表示字符串；
• 反引号：主要用在库名、表名、字段名，区分关键字，建议始终使用反引号表示。

mysql 注释语法
mysql 中支持 3 种注释语法：

# 这是单行注释
-- 这也是单行注释
/* 这是多行注释 */

字符集、字符序
character set：字符集，这个比较好理解，如 unicode 字符集，utf-8 字符编码。
collation：字符序，应该是 mysql 专有概念，主要作用是规定字符之间的比较顺序。

始终建议使用 utf8mb4 字符编码，mb4 即 multi byte 4，也就是标准的 utf-8 字符编码，mysql 中的 utf8 其实是阉割版，那时候 Unicode 还做着 65536 个字符统治全世界的美梦，这 6 万多个字符其实就是现在的 BMP 基本多文种平面，目前 Unicode 有 17 个平面，每个平面有 65536 个字符（码点），只有 utf8mb4 支持其它平面的字符，比如手机上的表情就是补充平面的字符。

而字符序的话，通常选择默认的即可，或者选择 unicode 规则的，分别是：
utf8_bin：二进制（区分大小写）
utf8_general_ci：默认字符序
utf8_unicode_ci：unicode 规则
utf8mb4_bin：二进制（区分大小写）
utf8mb4_general_ci：默认字符序
utf8mb4_unicode_ci：unicode 规则
一般 general 就够了，速度也是最快的，unicode 符合 unicode 语义，但速度略慢。
如果只指定 character set，那么 collation 会使用对应字符集的默认 collation 字符序。
大部分 charset 的默认 collate 都是 general_ci，其实我们不需要特别指定，默认的就好。

字符集深入
mysql 提供了不同级别的字符集配置，按照范围的从大到小顺序分别是：

• server 级：服务器默认字符集
• database 级：数据库默认字符集
• table 级：数据表默认字符集
• column 级：数据字段字符集

如果没有指定，那么会从最近的上级范围中继承字符集配置（包括字符序，下同）。注意，server 级、database 级、table 级的字符集配置都是拿来缺省用的，实际上 mysql 中的字符集只存在于 column 字段（也就是实际的数据）。server 级的字符集是创建的 database 的默认字符集，database 的字符集是创建的 table 的默认字符集，table 的字符集是其中未指定字符集的字段的默认字符集，都是一级一级传递下来的。最终有效的其实就是 column 字符集（char、varchar、text系列）。

查看支持的字符集
show character set;
show character set like 'utf8%';

查看支持的字符序
show collation;
show collation like 'utf8\_%';
show collation like 'utf8mb4%';
show collation where Charset = 'utf8';
show collation where Charset = 'utf8mb4';
字符序名都是以对应字符集名作为前缀的，如 utf8 字符集对应 utf8_general_ci 字符序。

server 级别
查看 server 字符集、字符序：

show variables like 'character_set_server';
show variables like 'collation_server';
show variables like '%\_server';

mysqld 运行时修改：

# 当前连接有效，重新连接会还原
set character_set_server = utf8mb4;
set session character_set_server = utf8mb4; # 同上
# 当前实例有效，重启数据库还原
set global character_set_server = utf8mb4;
# session 变量的优先级比 global 变量的优先级更高
# mysql 客户端在连接时，会拷贝一份 global 变量到同名 session 变量中
# 修改 mysql 变量时，如果需要全局生效，建议修改 global 和 session 变量
# 如果只修改 global 变量，mysql 客户端需要重新连接才能生效，因为存在同名 session 变量
# 如果只修改 session 变量，则只会影响当前连接的客户端，因为这只是会话变量，当前连接有效

mysqld 启动时指定：

mysqld --character-set-server=utf8mb4 # 使用字符集默认字符序
mysqld --character-set-server=utf8mb4 \
       --collation-server=utf8mb4_unicode_ci

配置文件中指定（建议）：

## utf8mb4 编码
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci

然后重启 mysqld.service 生效，使用 show variables like 'character%' 验证。

个人理解，其实配置文件就是更方便的命令行参数了，每次启动时从配置文件中读取参数。

database 级别
创建、修改数据库时指定：

# 指定字符集/字符序 create
create database <db_name>
    [default] character set <charset_name>
    [default] collate <collation_name>
# 修改字符集/字符序 alter
alter database <db_name>
    [default] character set <charset_name>
    [default] collate <collation_name>

查看 database 字符集/字符序：

show create database <db_name>;

table 级别

create table <table_name> (column_list)
    [default] character set <charset_name>
    collate <collation_name>
alter table <table_name>
    [default] character set <charset_name>
    collate <collation_name>

查看 table 字符集/字符序：

show create table <table_name>;

column 级别

# 创建时指定
<col_name> {char|varchar|text}(col_length)
    character set <charset_name>
    collate <collation_name>
# 添加时指定
alter table <table_name> add column <col_name> varchar(25) character set utf8mb4;
# 查看字符集
show full columns from <table_name>;

字符集再深入
查看 mysql 字符集状态信息有两种常用方法：
status;
show variables like 'charactet_set%';
status 查看的字符集信息比较简略，推荐使用后者查看：

MariaDB [(none)]> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

其中可以看到这些 character_set 信息：

• character_set_server：全局默认字符集，也即创建 database 时的默认字符集
• character_set_database：当前选择的数据库的字符集，如果没有则与 server 值相同
• character_set_connection：当前 mysql 连接器的字符集
• character_set_client：当前 mysql 客户端的字符集
• character_set_results：当前 mysql 结果集的字符集
• connection、client、results 的字符集强烈建议保持一致，推荐使用 utf8、utf8mb4

一般来说，只要 connection、client、results 三个一致，那么就不会出现乱码问题。设置方法：

# 分别设置
set character_set_client = charset_name;
set character_set_results = charset_name;
set character_set_connection = charset_name;
# 一次性设置（推荐）
# 一条语句等价于上面的三条语句
# 如果不指定 collate，则使用字符集对应的默认字符序
set names 'charset_name' [collate 'collation_name'];

注意，mysql 中也可以使用 charset 替代 character set，它们是等价的。
如：create database `test` charset utf8mb4 collate utf8mb4_general_ci;

变量的概念
mysql 中有三种变量类型，它们分别是：

• 系统变量：以 @@ 开头，分为 global、session 作用域
• 用户变量：以 @ 开头，作用域为 session 会话级别
• 局部变量：位于 BEGIN...END 语句块中，离开作用域被回收

global 全局作用域、session 会话作用域：

• global：变量存放在 mysqld 守护进程中
• session：变量存放在 mysql 客户端进程中

global 变量在多个 mysql 连接中有效，而 session 只在当前连接中有效。

mysql 在连接数据库时，会将 global 系统变量 dump 一份到同名 session 系统变量中，而 session 系统变量的优先级比 global 系统变量的优先级更高，所以修改系统变量时，如果想让它在当前会话中生效，则只设置 session 作用域的系统变量，如果想让它在全局生效，那么请同时设置 global 作用域和 session 作用域的系统变量，否则你需要重新连接数据库才能生效哦。

系统变量：

# 查看所有 global 变量
show global variables;
# 查看所有 session 变量
# session 变量包含 global 变量
show variables;
show session variables;
# 查看指定 global 变量
select @@global.variable_name;
show global variables like 'variable_name';
# 查看指定 session 变量
select @@variable_name;
select @@session.variable_name;
show variables like 'variable_name';
show session variables like 'variable_name';
# 修改指定 global 变量
set global variable_name = value;
set @@global.variable_name = value;
# 修改指定 session 变量
set variable_name = value;
set session variable_name = value;
set @@variable_name = value;
set @@session.variable_name = value;

用户变量：

# 设置用户变量
set @var1_name = value1, @var2_name = value2 ...;
# 查看用户变量
select @var1_name, @var2_name ...;

mysqld 启动时，会从 my.cnf 配置文件中初始化 global 变量，存放在内存之中。
因此，我们可以在运行时修改 global 变量，它的作用和配置文件中修改是一样的。
下次启动时 mysqld 仍从 my.cnf 中初始化变量，若想跨启动修改，请放在 my.cnf 中。

SQL 分为 4 个类别

• 数据定义语言 DDL：create、alter、drop
• 数据操纵语言 DML：select、insert、update、delete
• 数据控制语言 DCL：grant、deny、revoke
• 事务控制语言 TCL：commit、rollback、savepoint

DML 需要进行 commit 提交，否则所进行的操作都是没有生效的。与 commit 相对的是 rollback 回退，也就是撤销当前未 commit 的操作。当然你也可以在某个位置打个标记，即使用 savepoint <point_name>，然后你可以使用 rollback to <point_name> 来撤销到指定 point。如果未指定 point，则撤销自上次 commit 后的所有操作。使用 release savepoint <point_name> 删除回退点。在数据库领域，每次 commit 的语句序列被称为一个独立的事务（Transaction），事务是并发控制的基本单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。注意，每个事务都以上一个事务的 commit 开始，以当前事务的 commit 结束，因此每个 DML 语句都必然属于某个事务。每个事务都有自己独立的 savepoint 回退点，他们互不干扰，即使重名也不会有任何问题。使用 commit 提交后，该事务中的 savepoint 都会被丢弃，也就是说，commit 后你不能进行 rollback 撤销操作了。rollback 能操作的范围也仅限当前事务，因为他们还未 commit（你可以理解为当前未提交的操作都是在内存中进行的，而进行 commit 后才会写入到磁盘中。但实际上并不是这样的，数据库内部的机制远比想象的复杂，因为我也是 SQL 初学者，所以暂且先这么认为），rollback 给了你反悔的能力，它允许你撤销当前未提交的操作（或者回到某个 point，而不是全部都撤销）。还有一点要注意，如果是 rollback 撤销当前事务的所有未提交操作，那么相当于删除当前事务，也就是说这一步之后不需要 commit；但如果是 rollback to point，那么记得使用 commit 提交，不然该 point 前的操作都作废了。

MySQL 中默认启用 autocommit 自动提交功能，也就是说每个 DML 语句后都隐式的进行了 commit，也就是说默认情况下，每条 DML 语句都是一个独立的事务。如果需要关闭该行为，请使用 set autocommit = 0 来关闭它，使用 set global autocommit = 0 则表示全局关闭（直到下次重启 mysqld 进程）。

如果你不想关闭 autocommit 功能，也可以使用 start transaction 语句显式开始一个事务，该语句还有一个别名 begin，但是不建议使用 begin，因为这不是 SQL 标准语法。不论是不是启用了 autocommit，你都可以使用 start transaction 来显式开始一个事务，并且建议始终这么做。

为什么需要事务？
事务(Transaction)是并发控制的基本单位。所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。否则，可能出现一个账号扣了款，而另一个帐号没有收到钱的现象。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

SQL 事务有 4 个属性：原子性、一致性、隔离性、持久性。简称为 ACID（首字母缩写）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

注意，DML 中的 select 语句没有所谓的 commit 之分，只有 update、insert、delete 才适用。在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。Innodb 也是 MySQL、MariaDB 中的默认数据库引擎（除非你显式指定了其它数据库引擎）。

事务控制语句：

• BEGIN或START TRANSACTION：二者是等价的。显式地开启一个事务；
• COMMIT或COMMIT WORK：二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK或ROLLBACK WORK：二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier：SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；每个事务之间的 SAVEPOINT 互不影响，因为同时只会保存一个事务中的 SAVEPOINT。
• RELEASE SAVEPOINT identifier：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier：把事务回滚到标记点；
• SET TRANSACTION：用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

事务的概念
事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

存储引擎的概念
在 mysql 中的数据用各种不同的技术存储在文件（或内存）中。这些技术中的每一种技术都使用不同的存储机制，索引技巧，并且最终提供广泛的不同的功能和能力。可以通过选择不同的技术，可以获得额外的速度或功能，从而改善应用的整体功能。这些不同的技术以及配套的相关功能在 mysql 中被称为存储引擎（也称为表类型）。通过 show engines 来查看 mysql 支持的存储引擎。在 mysql 中用的最多的存储引擎有：innodb，bdb，myisam，memory 等。其中 innodb 和 bdb 支持事务而 myisam 等不支持事务。mysql 的默认存储引擎为 innodb。

事务的四个属性
1、原子性：事务是由一个或一组相互关联的SQL语句组成，这些语句被认为是一个不可分割的单元，要么都成功要么全回滚。
2、一致性：对于数据库的修改是一致的，即多个用户查的的数据是一样的。一致性主要由mysql的日志机制处理，他记录数据的变化，为事务恢复提供跟踪记录。
3、隔离性：每个事务都有自己的空间，和其他发生在系统中的事务隔离开来，而且事务的结果只在他完全被执行时才能看到
4、持久性：一但提交了这个事务之后对数据的修改更新就是永久的。当一个事务完成，数据库的日志已经被更新时，持久性即可发挥其特有的功效，在mysql中，如果系统崩溃或者数据存储介质被破坏，通过日志，系统能够恢复在重启前进行的最后一次成功更新，可以反应系统崩溃时处于执行过程的事物的变化。

事务的隔离性
现在重点来说明下事务的隔离性，当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，下面看看四种 隔离级别（级别由低到高）：

1. Read uncommitted 未提交读/脏读：该隔离级别的事务，在数据修改过程中，即使没有提交，其他事务对于这些数据也是可读的。事务可读到未提交的数据也叫脏读（Dirty Read），由于脏读在实际应用中会导致很多问题，一般这类隔离级别应用很少。
2. Read committed 已提交读/不可重复读：不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在该事务的两次查询间隔内被另一个事务修改并提交了。例如事务 T1 在读取某一数据，而事务 T2 立马修改了这个数据并且提交事务给数据库，事务 T1 再次读取该数据就得到了不同的结果，发生了不可重复读。不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。该隔离级别也是一般数据库的默认级别，如 Oracle。只有当前事务执行完，把数据提交之后，其他事务才可对这些数据进行读取。也叫不可重复读，因为其他事务执行 2 次查询可能前后会得到 2 个不同结果（事务执行前读一次，执行后读一次）。
3. Repeatable read 可重复读/幻读：幻读是事务非独立执行时发生的一种现象。例如事务 T1 对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务 T2 又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务 T1 的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务 T2 中添加的，就好像产生幻觉一样，这就是发生了幻读。幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。该隔离级别是 Mysql 的默认隔离级别，它解决了脏读的问题，该级别保证了在同一事务中，多次读取的结果是一致的。但仍旧无法解决幻读问题。幻读：事务 A 在读取一定范围内数据时，事务 B 有对该范围数据进行插入等更新操作，事务 A 再次读取该范围记录时，会产生幻读。
4. Serializable 可串行化：在读取每一行数据的时候都对该行数据加锁，强制事务串行执行，在事务提交的时候会释放锁，这时其他事务才能获取相关数据的锁进行处理这样避免了幻读问题，隔离性达到了最高但是这种执行方式效率低，锁竞争激烈容易出现超时问题！只有在对数据一致性要求很强的时候和没有并发或并发量很小的时候使用！

最常用的其实就是级别二和级别三，即 Read Committed 和 Repeatable Read。其中已提交读是大部分数据库的默认隔离级别，但是 MySQL 却选择了更高一级的可重复读。注意：级别越高，执行效率就越低，锁竞争就越激烈，还容易出现锁竞争超时问题。

这几个隔离级别究竟有什么区别呢？估计你看了上面的介绍也还是不太明白它们的具体区别。这是另一篇文章的介绍，我觉得介绍得更加明了一些，每个事务隔离级别其实都比上一级多解决了一个问题，总共有 3 个问题，而第一个级别没有解决任何问题，实际上它也非常少用：

• RAED UNCOMMITED：使用查询语句不会加锁，可能会读到未提交的行（Dirty Read）；
• READ COMMITED：只对记录加记录锁，而不会在记录之间加间隙锁，所以允许新的记录插入到被锁定记录的附近，所以再多次使用查询语句时，可能得到不同的结果（Non-Repeatable Read）；
• REPEATABLE READ：多次读取同一范围的数据会返回第一次查询的快照，不会返回不同的数据行，但是可能发生幻读（Phantom Read）；
• SERIALIZABLE：InnoDB 隐式地将全部的查询语句加上共享锁，解决了幻读的问题；

其中 InnoDB 实现可重复读的基本原理就是快照（或者可以理解为缓存，后面的读取操作其实都没有读取实际的数据，而是读取第一次返回的快照/缓存而已），这个技术的名称为 MVCC（多版本并发控制），其实就是保存的快照（个人简单理解）。

事务隔离级别的查看与修改

# 查看事件隔离级别
select @@global.tx_isolation;
select @@session.tx_isolation;
show global variables like 'tx_isolation';
show session variables like 'tx_isolation';
# 修改事件隔离级别
set global transaction isolation level SERIALIZABLE;
set session transaction isolation level SERIALIZABLE;
# 其中级别有 4 个，分别为：
# READ UNCOMMITTED
# READ COMMITTED
# REPEATABLE READ
# SERIALIZABLE

隔离级别的总结
1、Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
2、Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
3、Read committed (读已提交)：可避免脏读的发生。
4、Read uncommitted (读未提交)：都无法保证。

笔记相关说明
<option>：表示必选参数，其中 <> 只是用来说明的，不用实际输入；
[option]：表示可选参数，其中 [] 只是用来说明的，不用实际输入；
{opt1|opt2}：表示二者都可以，{|} 只是用来说明的，不用实际输入；
如未特殊说明，所有语句都以 ; 分号结尾，本篇主要以小写关键字为主。

库操作

创建数据库
create {database|schema} [if not exists] <db_name> [db_opts]

• {database|schema}：在 mysql 中没有区别，习惯使用 database
• [if not exists]：如果指定 database 不存在则创建，否则不执行
• <db_name>：数据库名称，一般建议小写，如 mysql、wordpress
• [db_opts]：数据库字符集、字符序（校对集），它们分别是：
  
  • [default] character set <charset_name>：default 可有可无
  • [default] collate <collation_name>：default 可有可无，默认为字符集的默认字符序

数据库名不能使用关键字、保留字，如果非要使用，必须使用反引号将数据库名括起来。数据库名不建议使用中文，应始终使用 ascii 字符，避免各种各样的问题，为了提高 sql 语句的执行性，建议始终添加反引号，与 mysql 关键字、保留字进行区分。

查询数据库
show databases;：查看所有数据库
show databases like 'pattern';：模糊匹配，pattern 中有两个通配符
show create database <db_name>;：查看创建数据库的语句（会进行优化）

• %：表示匹配任意多个字符，同 shell 中的 *，百分号本身使用 \%；
• _：表示匹配任意单个字符，同 shell 中的 ?，下划线本身使用 \_。

pattern 的匹配规则与 shell 中的 glob 一样，要求整个字符串匹配，即 ^pattern$。

更新数据库
注意，数据库名称不能修改，只能导出再导入进行改名。数据库的修改仅限字符集、字符序。

# db_name 如果未指定，则使用当前 use 的 db
alter {database|schema} [db_name]
    [default] character set [=] charset_name
    [default] collate [=] collation_name

删除数据库

drop database <db_name>;

表操作

创建数据表

create table [if not exists] <table_name> (
    <field1_name> <field1_datatype>,
    <field2_name> <field2_datatype>,
    <field3_name> <field3_datatype>,
    ...
    <fieldN_name> <fieldN_datatype> # 最后一个字段没有逗号
) [table_opts];
# [table_opts] 常用
character set [=] charset_name
collate [=] collation_name
engine [=] engine_name

任何数据表都属于某个数据库，创建表时必须指定所属的数据库，有两种方式：

# 显式指定
create table if not exists test.student (
    name varchar(10),
    age int,
    grade varchar(10)
) charset utf8;
# 隐式指定
use test;
create table if not exists student (
    name varchar(10),
    age int,
    grade varchar(10)
) charset utf8;

查询数据表

use test;                           # 使用指定数据库
show tables;                        # 查看当前所有表
show tables like 'pattern';         # 查看当前指定表
desc <table_name>;                  # 查看表字段信息 
describe <table_name>;              # 查看表字段信息（同上）
show columns from <table_name>;     # 查看表字段信息（同上）
show full columns from <table_name> # 查看表字段详细信息（如字符集/序）
desc <table_name> {col_name|col_wild};      # 查看指定字段信息
describe <table_name> {col_name|col_wild};  # 查看指定字段信息
show [full] columns {from | in} tbl_name [{from | in} db_name]
    [like 'pattern' | where expr]
show create table <table_name>;     # 查看表创建信息

关于 mysql 的显示格式，;、\g、\G，我们先来看个例子：

MariaDB [test]> select User,Host,Password from mysql.user;
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | ::1       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
MariaDB [test]> select User,Host,Password from mysql.user\g
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | ::1       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
MariaDB [test]> select User,Host,Password from mysql.user\G
*************************** 1. row ***************************
    User: root
    Host: localhost
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
*************************** 2. row ***************************
    User: root
    Host: 127.0.0.1
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
*************************** 3. row ***************************
    User: root
    Host: ::1
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
3 rows in set (0.00 sec)

可以看出，分号和 \g 是一样的，它们完全相同，显示格式可以概括为 二维表形式，而 \G 则是一行一行显示，相当于旋转了 90^o，每次显示一条记录（一行），看情况了，一般哪个合适选哪个。

另外，还可以使用 show table status [from db_name] [like 'pattern']\G 来查看表的详细信息。

MariaDB [test]> show table status\G
*************************** 1. row ***************************
           Name: student
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 6
    Create_time: 2018-08-10 13:43:05
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 2. row ***************************
           Name: test0
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9
 Avg_row_length: 1820
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 10
    Create_time: 2018-08-10 13:43:05
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 3. row ***************************
           Name: test1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9
 Avg_row_length: 1820
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 10
    Create_time: 2018-08-10 13:43:05
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
3 rows in set (0.00 sec)

更新数据表

表的修改分为：修改表本身、修改表字段。

修改表本身

• 修改表名，基本语法：rename table <old_name> to <new_name>;
• 修改表选项，基本语法：alter table <table_name> <table_opts>;

修改表字段

• 添加字段
  
  • alter table <tab_name> add [column] <col_name> <data_type> [col_opts] [col_pos];
  • col_pos 表示在哪个位置添加字段，first 表示第一个位置，after <col_name> 表示在指定字段后面，如果未指定，则添加到末尾处。
  • 例子：alter table `student` add column `id` int first;
• 修改字段
  
  • alter table <tab_name> modify [column] <col_name> <data_type> [col_opts] [col_pos];
  • 例子：alter table `student` modify column `class` varchar(10) after `name`;
• 重命名字段
  
  • alter table <tab_name> change [column] <old_col_name> <new_col_name> <data_type> [col_opts] [col_pos];
  • 例子：alter table `student` change column `score` `grade` float(4, 1);
• 删除字段
  
  • alter table <tab_name> drop [column] <col_name>;
  • 例子：alter table `student` drop column `age`;

删除数据表
语法：drop table [if exists] <table_name>[, table_name ...];

临时表
临时表和普通表在使用上没有什么区别，主要的区别是生命周期，普通表除非你使用 drop table 显式删除，否则不会凭空消失。而临时表，顾名思义，生命周期只限定于当前 MySQL 连接，连接被释放后，属于该链接的所有临时表都会被自动删除。临时表可以和普通表同名，此时只能看到临时表，因为被名称屏蔽了，只有在该临时表被删除后才能看到普通表。

创建临时表的语法：create temporary table (...)，多个 temporary 关键字，其它基本相同。
使用临时表的语法：基本上和普通表一样，select、insert、delete、update、alter 都差不多。
查看临时表的语法：使用 show tables 看不到临时表，只能通过 show create table 表名 查看。
删除临时表的语法：drop table 临时表，语法和删除普通表一样。

表的复制

## 完整复制（结构、索引、数据）
CREATE TABLE newtable LIKE oldtable; 
INSERT INTO newtable SELECT * FROM oldtable;
## 只拷贝表数据（从二维表创建）
CREATE TABLE tbl_new SELECT * FROM tbl_old;

数据操作

数据操作也称为记录操作，row、record 操作。添加/删除/修改/查询 记录。

添加记录
添加全部字段：insert into <table_name> values(value1, value2, ...);
添加部分字段：insert into <table_name>(field1, field2, ...) values(value1, value2, ...);
无论哪种方式，都必须保持顺序的一致，字符串建议使用单引号括住，数值数据不需要也不能。

查询记录
查询全部字段：select * from <table_name> [where condition];
查询部分字段：select col_name[,col_name,...] from <table_name> [where condition];

where 中的 condition 语法：condition_1 [and|or] condition_2 ...，其中条件一般用于测试字段，如 grade >= 80 表示成绩优秀的人有哪些，支持的比较运算符有：

• = 是否相等
• <> 或 != 是否不相等
• < 是否小于
• <= 是否小于等于
• > 是否大于
• >= 是否大于等于

除此之外，还有两个模糊查询（针对字符串），如果算上取反，那就有 4 个：
like：sql 通配符，_ 单个字符，% 任意多个字符，隐含 ^pattern$
not like：sql 通配符取反
regexp：sql 正则表达式，没有隐含 ^pattern$
not regexp：sql 正则表达式取反

多个条件之间可以使用逻辑连接符 and、or 连接，表示逻辑与、逻辑或。

sql 支持对查询结果排序，使用关键字 order by，基本语法为：
select * from <table_name> order by col_name1 [asc|desc], col_name2 [asc|desc] ...;
asc 为升序，desc 为降序，默认为升序。可指定多个排序字段，如果前面的相等，则比较后面的。

null值的处理
在 MySQL 中，NULL 值是比较特殊的，你不能使用 = NULL、!= NULL、<> NULL 来判断 NULL 值，因为 NULL 与任何值进行比较都返回 false，包括 NULL 自己，也就是 NULL = NULL 也返回 false。正确判断 NULL 值的方法是使用 IS NULL、IS NOT NULL，或者使用 <=> 操作符替代 =，它可以正确处理 NULL 值，当两边的值都为 NULL 时它会返回 true。

更新记录
update <table_name> set col_name = new_value, col_name = new_value ... [where condition];
如果没有指定 where 条件，那么操作全表的数据，这可能不是你想要的；判断更新操作是否成功不是单看 SQL 语句是否执行成功，要看返回的 affected（受影响的）记录数量是否大于等于 1。

删除记录
删除部分记录：delete from <table_name> [where condition]
删除全部记录：delete from <table_name> 或 truncate table <table_name>（推荐）

数值类型

SQL 将数据类型分为三大类，分别为：数值型、字符串型、日期时间型。

对于数值型数据，可以进一步分为 整数型 和 小数型。

整数型
在 SQL 中由于要考虑节省磁盘空间的问题，因此系统又将整型细分为 5 类，分别为：

• tinyint：迷你整型，使用 1 个字节存储数据（常用）；
• smallint：小整型，使用 2 个字节存储数据；
• mediumint：中整型，使用 3 个字节存储数据；
• int：标准整型，使用 4 个字节存储数据（常用）；
• bigint：大整型，使用 8 个字节存储数据。

MySQL 中的整数默认都是有符号的（标准 SQL 只能是有符号数，没有所谓的 unsigned 无符号数），如果在整数型后使用 unsigned 进行修饰，那么表示这是一个无符号数。无符号数只能表示正数，无符号数可以表示正数和负数。

例如 tinyint，一个字节长度，默认的有符号数可表示的范围为 [-128, 127]，添加了 unsigned 的 tinyint 可表示的范围为 [0, 255]。虽然都是 2^8 = 256 个数字，但是如果你的数据全都是正数（如用户 ID），可以考虑使用无符号数。不过使用无符号数会给可移植性带来一些问题，因为其他数据库基本都不支持无符号数。而且 mysql 中使用无符号数有时候也会遇到非常诡异的问题，因此不建议使用无符号数，因为同样的数据长度的情况下，无符号也仅比有符号的大一倍而已，实际上没多少，还不如使用更长的数据类型呢。

无论是有符号数还是无符号数，都是有大小范围的，如果存入的数值大于最大值则发生上溢，如果存入的数值小于最小值则发生下溢，它们都统称为溢出。发生上溢时，数值为 max 值，发生下溢时，数值为 min 值。同时 mysql 会产生警告信息，使用 show warnings 可查看上一个语句中的 warning 信息。

例子：

create table int_test(
    int_1 tinyint,
    int_2 smallint,
    int_3 mediumint,
    int_4 int,
    int_8 bigint
);

使用 desc 查看字段信息：

MariaDB [test]> desc int_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| int_1 | tinyint(4)   | YES  |     | NULL    |       |
| int_2 | smallint(6)  | YES  |     | NULL    |       |
| int_3 | mediumint(9) | YES  |     | NULL    |       |
| int_4 | int(11)      | YES  |     | NULL    |       |
| int_8 | bigint(20)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

可以发现，每个字段的数据类型后面都会跟一个圆括号，里面还有一个数字。注意这个数字表示的是数值的显示宽度，与其他数据类型后面的圆括号里面的数字的意义是不一样的，其他数据类型里面的数字表示的是数据长度（字节）。这个显示宽度在一般情况下是没有任何使用和显示上的区别的，也就是说 int(1) 和 int(11) 的区别在一般情况下看不出来，它们的数据长度都是 4 个字节。除非你使用了 zerofill 修饰（zerofill 修饰会将数据类型变为无符号数），zerofill 的意思是不足宽度的位置使用 0 补齐（和 printf 中的格式对齐是一样的道理）。请看例子：

MariaDB [test]> create table int_test(a int(4), b int(4) zerofill);
Query OK, 0 rows affected (0.10 sec)
MariaDB [test]> desc int_test;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| a     | int(4)                   | YES  |     | NULL    |       |
| b     | int(4) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [test]> insert into int_test values(1, 1);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> select * from int_test;
+------+------+
| a    | b    |
+------+------+
|    1 | 0001 |
+------+------+
1 row in set (0.00 sec)
MariaDB [test]> insert into int_test values(1000, 1000);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> select * from int_test;
+------+------+
| a    | b    |
+------+------+
|    1 | 0001 |
| 1000 | 1000 |
+------+------+
2 rows in set (0.00 sec)
MariaDB [test]> insert into int_test values(100000, 100000);
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> select * from int_test;
+--------+--------+
| a      | b      |
+--------+--------+
|      1 |   0001 |
|   1000 |   1000 |
| 100000 | 100000 |
+--------+--------+
3 rows in set (0.00 sec)

为了可阅读性，建议使用 zerofill 时这样声明字段的数据类型：int(8) unsigned zerofill。
但是，实际应用中不建议使用 unsigned 和 zerofill，尽量按标准行事（SQL 的标准，便于移植）。也不建议指定什么显示宽度，比如 int 就写作 int，bigint 就写作 bigint，不要加什么括号。

小数型
小数型，即带有小数点或者范围超出整型的数值类型。
在 SQL 中，小数型可细分为浮点型和定点型两种，其中：

• 浮点型：小数点浮动，精度有限，容易丢失精度；
• 定点型：小数点固定，精度固定，不会丢失精度。

第 1 种：浮点型
浮点型数据是一种精度型数据，因为超出指定范围之后，其会丢失精度，自动进行四舍五入操作。理论上，浮点型分为两种精度：

• float：单精度，占用 4 个字节存储数据，精度只保证 7 位有效数字；
• double：双精度，占用 8 个字节存储数据，精度只保证 15 位有效数字。

float/double 声明：float(M, D)、double(M, D)，其中 M 代表总长度，D 代表小数部分长度，M-D 则为整数部分长度（M 最大 255，D 最大 30）。向 float/double 赋值时，可以直接插入小数（如 3.14），也可以插入用科学计数法表示的数据（如 2.18E3）。此外，插入浮点型数据时，整数部分是不能超出长度范围的，但是小数部分是可以超出长度范围的，系统会自动进行四舍五入的操作。特别的，如果浮点数是因为系统进位（四舍五入）导致整数部分超出指定的长度，那么是允许的。经测试（mariadb），不可以，会产生警告，实际存储的的值也不会超过 M，D 的限制。例子：

MariaDB [test]> desc test2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| float | float(2,1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [test]> insert into test2 values(9.9);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> select * from test2;
+-------+
| float |
+-------+
|   9.9 |
+-------+
1 row in set (0.00 sec)
MariaDB [test]> insert into test2 values(9.99);
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [test]> show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1264 | Out of range value for column 'float' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [test]> select * from test2;
+-------+
| float |
+-------+
|   9.9 |
|   9.9 |
+-------+
2 rows in set (0.00 sec)
MariaDB [test]> insert into test2 values(9.9999);
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [test]> show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1264 | Out of range value for column 'float' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [test]> select * from test2;
+-------+
| float |
+-------+
|   9.9 |
|   9.9 |
|   9.9 |
+-------+
3 rows in set (0.00 sec)

第 2 种：定点型
类型声明：decimal(M, D)，其中 M 代表总长度，D 代表小数部分长度，M-D 则为整数部分长度。M 最大值为 65，D 最大值为 30。

定点型数据，绝对的保证整数部分不会被四舍五入，也就是说不会丢失精度，但小数部分有可能丢失精度，虽然理论上小数部分也不会丢失精度（这里的意思是只要小数部分的长度未超过声明时的长度就不会出现精度丢失的问题，MySQL 允许你插入超过声明时指定的小数部分长度，自动进行四舍五入操作）。

tinyint、smallint、mediumint、int、bigint、float、double、decimal 都可以使用 unsigned、zerofill 修饰，但是对于小数类型（float、double、decimal）强烈不建议使用 unsigned、zerofill 修饰，对于整数类型，也不是特别建议使用，除非真的需要。

日期时间型

日期时间型数据，顾名思义，就是用来表示日期和时间的数据类型，共有 5 种类型，分别为：

• datetime：日期时间，8 个字节，格式 yyyy-MM-dd HH:mm:ss，范围 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，存储方式 yyyyMMddHHmmss，不保存时区信息。
• timestamp：Unix 时间戳，4 个字节，范围 1970-2038 年，存储自 1970-01-01 00:00:00 UTC 起的秒数。timestamp 会根据当前时区（系统/mysql）自动显示对应时间（格式同 datetime）。timestamp 还有一个特殊属性：自动更新，可利用此属性存储记录的最后修改时间。
• date：日期，即 datetime 的 date 部分。
• time：时间，即 datetime 的 time 部分，可以为负值（为了方便运算）。
• year：年份，有两种格式 year(2)、year(4)，强烈建议使用 4 位数的形式。不过 mariadb 好像不建议自定义长度了，一律使用 year(4) 格式，但是要使用 year 进行声明，否则产生警告。

一般使用 datetime 即可，可以使用各种日期时间函数。如果不关心 date，则使用 time，反之可以使用 date。year 单独使用的比较少（个人认为），timestamp 用的也不是很多，因为它会自动更新，比较适合的地方也就是最后的记录修改时间（lastmodify time），而 datetime 一般适合存储记录的创建时间（create time）。也有人使用 int、bigint 存储 unixtime（时间戳），具体的方式是通过函数 unix_timestamp() 获取 unix 时间（整数，存储到字段中），显示时可以使用 from_unixtime() 函数解析 unixtime，显示的格式与 datetime 相同。

timestamp 显示的时间与当前设置的时区精密相关，看例子：

MariaDB [test]> create table `dt_test`(`datetime` datetime, `timestamp` timestamp);
Query OK, 0 rows affected (0.10 sec)
MariaDB [test]> insert into `dt_test` values(now(), now());
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> select * from `dt_test`;
+---------------------+---------------------+
| datetime            | timestamp           |
+---------------------+---------------------+
| 2018-07-26 21:14:41 | 2018-07-26 21:14:41 |
+---------------------+---------------------+
1 row in set (0.00 sec)
MariaDB [test]> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from `dt_test`;
+---------------------+---------------------+
| datetime            | timestamp           |
+---------------------+---------------------+
| 2018-07-26 21:14:41 | 2018-07-26 13:14:41 |
+---------------------+---------------------+
1 row in set (0.00 sec)
MariaDB [test]> set time_zone='+08:00';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from `dt_test`;
+---------------------+---------------------+
| datetime            | timestamp           |
+---------------------+---------------------+
| 2018-07-26 21:14:41 | 2018-07-26 21:14:41 |
+---------------------+---------------------+
1 row in set (0.00 sec)

关于 timestamp 的自动更新，自动更新属性默认是启用的，insert 时如果没有给 timestamp 字段赋值，那么 mysql 会自动将当前的时间赋给该字段，同时 update 记录时如果没有给 timestamp 字段赋值，那么 mysql 也会自动将当前的时间赋给该字段，因此利用 timestamp 可以很容易实现 last modify 时间。例子：

MariaDB [test]> select * from dt_test;
+---------------------+---------------------+
| datetime            | timestamp           |
+---------------------+---------------------+
| 2018-07-26 21:14:41 | 2018-07-26 21:14:41 |
+---------------------+---------------------+
1 row in set (0.00 sec)
# 没有插入任何值，但是 timestamp 依旧会自动更新
MariaDB [test]> insert into dt_test values();
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> select * from dt_test;
+---------------------+---------------------+
| datetime            | timestamp           |
+---------------------+---------------------+
| 2018-07-26 21:14:41 | 2018-07-26 21:14:41 |
| NULL                | 2018-07-26 21:19:55 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
# 注意判断是否为 null 要使用 is null，判断不是 null 要使用 is not null
MariaDB [test]> update dt_test set datetime = now() where datetime = NULL;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
MariaDB [test]> select * from dt_test;
+---------------------+---------------------+
| datetime            | timestamp           |
+---------------------+---------------------+
| 2018-07-26 21:14:41 | 2018-07-26 21:14:41 |
| NULL                | 2018-07-26 21:19:55 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
MariaDB [test]> update dt_test set datetime = now() where datetime is NULL;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [test]> select * from dt_test;
+---------------------+---------------------+
| datetime            | timestamp           |
+---------------------+---------------------+
| 2018-07-26 21:14:41 | 2018-07-26 21:14:41 |
| 2018-07-26 21:21:20 | 2018-07-26 21:21:20 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

最后提一下，datetime、timestamp 类型可以插入字符串、整数（字符串缩写形式），例子：

MariaDB [test]> select * from dt_test;
Empty set (0.00 sec)
MariaDB [test]> insert into dt_test(datetime, timestamp, date, time) values(now(), now(), curdate(), curtime());
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into dt_test(datetime, timestamp, date, time) values('2018-10-01 12:00:00', '2018-10-01 12:00:00', '1998-12-15', '09:18:00');
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into dt_test(datetime, timestamp, date, time) values('20181001120000', '20181001120000', '19981215', '091800');
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> insert into dt_test(datetime, timestamp, date, time) values(20181001120000, 20181001120000, 19981215, 091800);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> select * from dt_test;
+---------------------+---------------------+------------+----------+
| datetime            | timestamp           | date       | time     |
+---------------------+---------------------+------------+----------+
| 2018-07-26 21:38:58 | 2018-07-26 21:38:58 | 2018-07-26 | 21:38:58 |
| 2018-10-01 12:00:00 | 2018-10-01 12:00:00 | 1998-12-15 | 09:18:00 |
| 2018-10-01 12:00:00 | 2018-10-01 12:00:00 | 1998-12-15 | 09:18:00 |
| 2018-10-01 12:00:00 | 2018-10-01 12:00:00 | 1998-12-15 | 09:18:00 |
+---------------------+---------------------+------------+----------+
4 rows in set (0.00 sec)

字符串类型

• char，文本、定长字符串：char(N)，N <= 255，单位为字符，有字符集/字符序属性，可以有 default 值。如果存入的数据尾部有空格则被自动删除，然后再存入，如果长度不足则自动使用空格填充，取出数据时自动忽略填充的空格符。char 和 varchar 这两个以字符为单位的数据类型的具体长度等于 N * length of charset，比如 utf8 字符集为 N * 3，utf8mb4 字符集为 N * 4。
• varchar，文本、变长字符串：varchar(N)，N <= 65535，单位为字符，有字符集/字符序属性，可以有 default 值。因为是变长的，所以需要额外的 1、2 字节存储实际数据的长度，如果字符串长度不超过 255，则使用 1 byte，如果超过 255，则使用 2 byte。虽然 varchar 的长度最大可以为 65535 字符，但是 mysql 会将它转换为 text 存储，而且因为 mysql 表中的所有字段的长度加起来不能超过 65535 byte，所以过大很容易导致建表失败。一个建议是 char 和 varchar 只用来存储少于 255 个字符的数据，超过此值的数据使用 text 来存储。text/blob 字段的实际数据不存储在表内的，类似于 C 语言中的指针，text/blob 存储的只是一个位置，实际数据在外部，所以不会导致 row size too large 错误。
• binary，二进制、定长字符串：binary(L)，L <= 255，单位为字节，没有字符集/字符序属性，可以有 default 值。如果存入的数据长度不足，则自动使用 0x00 字符填充，取数据时会一并取出 0x00，特别注意。binary 和 varbinary 可以存储的字符个数要看使用的什么字符集，假设是 10 字节，则可以存储 10 个 ASCII 字符（utf8、utf8mb4），或者存储 3 个中文（BMP） + 1 个英文（utf8、utf8mb4）。
• varbinary，二进制、变长字符串：varbinary(L)，L <= 65535，单位为字节，没有字符集/字符序属性，可以有 default 值。binary/varbinary 与 char/varchar 基本类似，很多属性、特点可以类推。比如 varbinary 的长度如果太大 mysql 会自动将其转换为 blob（可以知道 char/varchar 对应 text，binary/varbinary 对应 blob，前者主要存储不超过 255 个长度的数据，后者则用于存储大数据）。
• text，文本、变长字符串（一般）：0 ~ 65535 字节，实际存储的数据在外部，该字段的内容其实是实际数据的位置，大概占 9 ~ 12 字节。不能设置 default 默认值，有 charset/collate 属性。
• tinytext，文本、变长字符串（极小）： 0 ~ 255 字节，其余同 text。
• mediumtext，文本、变长字符串（中等）：0 ~ 16777215 字节，其余同 text。
• longtext，文本、变长字符串（极大）：0 ~ 4294967295 字节，其余同 text。
• blob，二进制、变长数据（一般）：0 ~ 65535 字节，实际存储的数据在外部，该字段的内容其实是实际数据的位置，大概占 9 ~ 12 字节。不能设置 default 默认值，没有 charset/collate 属性。
• tinyblob，二进制、变长数据（极小）：0 ~ 255 字节，其余同 blob。
• mediumblob，二进制、变长数据（中等）：0 ~ 16777215 字节，其余同 blob。
• longblob，二进制、变长数据（极大）：0 ~ 4294967295 字节，其余同 blob。

mysql 每个表的字段数不能超过 4096 个，所有字段的长度加起来不能超过 65535 字节

字符串类型总结：char 系对应 text 系，binary 系对应 blob 系，前者支持 charset/collate 设置，后者不支持。char 系和 binary 系都是存储小数据用的，分别有定长和不定长之选，数据的长度一般不超过 255 单位长度（字符/字节），如果很长，请选择对应的 text、blob 数据类型，char 系和 binary 系都可以设置默认值，且效率比 text/blob 系好，如果可以尽量考虑 char 系、binary 系。基本上所有字段的数据都是内联在数据表中的，除了 text 和 blob 类型，因为他们很有可能是非常大的数据（G 级别），所以 mysql 将 text/blob 的实际数据存储在表外，本身只存储它们的具体位置。

eg：姓名、地址等变长信息优先使用 varchar，身份证号、电话号码等建议使用 char 定长字符串。

• char -> varchar -> text系列：使用字符作为长度单位，字符数据，可指定字符集、字符序。
• binary -> varbinary -> blob系列：使用字节作为长度单位，二进制数据，没有字符集设置。

枚举类型 enum
类似单选题，只能选择已定义的选项中的一个，内部使用整数序号存储，节省存储空间，可以规范输入数据，但是因为存在额外的查找对比过程所以效率略低一些，这是 enum 的例子：

MariaDB [test]> create table t1 (name char(4), gender enum('男', '女', '未知'));
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> insert into t1 values('张三', '男');
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into t1 values('李四', '男');
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> insert into t1 values('如花', '女');
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into t1 values('小明', '未知');
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> select * from t1;
+--------+--------+
| name   | gender |
+--------+--------+
| 张三   | 男     |
| 李四   | 男     |
| 如花   | 女     |
| 小明   | 未知   |
+--------+--------+
4 rows in set (0.00 sec)
MariaDB [test]> select name, gender, gender+0 from t1;
+--------+--------+----------+
| name   | gender | gender+0 |
+--------+--------+----------+
| 张三   | 男     |        1 |
| 李四   | 男     |        1 |
| 如花   | 女     |        2 |
| 小明   | 未知   |        3 |
+--------+--------+----------+
4 rows in set (0.00 sec)

枚举在进行数据规范（定义）的时候，系统会自动建立一个数字与枚举元素的对应关系（放在日志中）；在进行数据插入的时候，系统自动将字符串转换为对应的数值进行存储；在进行数据提取的时候，系统自动将数值转换成对应的字符串进行显示。

集合类型 set
set 和 enum 类似，存储的是数值而不是字符串，类似多选题（当然可以单选）。它们的特点都是一样的，优点是节省空间&规范数据，缺点是效率不是很高。这是使用例子：

MariaDB [test]> create table t2 (color set('红', '橙', '黄', '绿', '青', '蓝', '紫'));
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> insert into t2 values ('红'), ('红,黄,蓝'), ('绿,紫');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
MariaDB [test]> select * from t2;
+-------------+
| color       |
+-------------+
| 红          |
| 红,黄,蓝    |
| 绿,紫       |
+-------------+
3 rows in set (0.00 sec)
MariaDB [test]> select color+0, color from t2;
+---------+-------------+
| color+0 | color       |
+---------+-------------+
|       1 | 红          |
|      37 | 红,黄,蓝    |
|      72 | 绿,紫       |
+---------+-------------+
3 rows in set (0.00 sec)

这里稍微解释一下内部存储的数值的意义：集合字符串中每一个元素都对应一个二进制位，其中被选中的为 1，未选中的为 0，最后再反过来，这个二进制数对应的十进制数即为其数据库中实际存储的是数值。

再谈字符集
人与人之间通常使用“字符”进行交流，但是计算机却只认识二进制（数字），如果想在计算机中存储“字符”，那么我们必须使用对应的数字表示我们的“字符”。每个数字都只能对应一个字符，每个字符也只能对应一个数字，要呈一对一关系，不然转换时必定出错。这个 字符 <-> 数值 的映射关系我们称为字符集。简单的说，字符集就是一张表，规定了每个字符对应的数值是多少，这样在将字符存入计算机、从计算机中取出字符时才能正确的显示。

历史的发展中出现了很多字符集，最早的是 ASCII 字符集，ASCII 字符集只包含了美国人使用的字符，即：33 个控制字符、 26 + 26 个英文字母（大写、小写），10 个阿拉伯数字，以及常用的标点符号。总共也就 128 个字符而已（7 个比特位）。注意，我们把字符集中的每个字符对应的数值都称为一个码点，一个码点就代表字符集中的一个字符，比如 ASCII 有 128 个字符，也就是说 ASCII 字符集有 128 个码点（code point）。

但是很明显 ASCII 只能够美国人使用，像西欧、CJK （中日韩）字符，很多字符都没有囊括在其中，那怎么办，西欧人想到一个办法，ASCII 字符集不是只使用了 1 个字节中的 7 个比特位吗，还有一个位（128 个空闲的码点），我们可以利用这 128 个空闲的码点来存储我们的字符，不就可以了吗。这就是后来的 EASCII 字符集（扩展的 ASCII 字符集），后来发展为了 ISO 8859-1 字符集，正式编号为 ISO/IEC 8859-1:1998，又称 Latin-1 或“西欧语言”，即使用 1 byte 的字符集。

那么我们中文怎么办，貌似剩下的 128 个码点也不够表示我们的汉字啊（常用的汉字有 3500+ 多个呢），没关系，我们使用两个字节来存储，这样就有 2¹⁶ = 65536 个码点了，前 8 bit 与 EASCII 相同，后面的几万个码点就拿来存储汉字（简体、繁体，等等）。类似的，其他国家也使用类似的方法创建了属于自己的字符集，可谓是百花齐放。

但是，各种各样的字符集却带来了很大的麻烦 - 乱码。因为同一个数值，使用不同的字符集来解释显然会得到不同的字符，于是就产生了乱码。而且各大字符集都互不兼容，你弄你的标准，我玩我的标准。为了快点结束这种混乱的局面，Unicode 字符集出现了，它号称要表示世界上的所有字符，将它们都容纳在 Unicode 字符集中，因此也成为万国码、统一码。Unicode 至今仍在不断增修，每个新版本都加入更多新的字符。目前最新的版本为 2018 年 6 月 5 日公布的 11.0.0，已经收录超过 13 万个字符（第十万个字符在 2005 年获采纳）。Unicode 涵盖的数据除了视觉上的字形、编码方法、标准的字符编码外，还包含了字符特性，如大小写字母。因为 Unicode 字符数量庞大，为了方便管理，将其划分为了 17 个平面（plane），每个平面都有 65536 个码点。总共有 65536 * 17 = 1,114,112 个码点，111 万个码点，表示所有的字符应该是足够了。不过目前只使用了很少的一部分，平面的编号从 0 开始，一直到 16。0 号平面称为“基本多文种平面” BMP，其中包含了绝大多数常用的字符，包括 CJK 字符。而 1 号平面则称为“多文种补充平面” SMP，复杂的汉字就包含在其中。BMP 和 SMP 总共 12 万个码点/字符，日常使用中 99.99% 的字符都来自其中。

Unicode 字符集的出现确实解决了字符集不兼容的局面，但是一个新的问题又来了，我们该如何存储这 111 多万个码点呢？如果要全部装下，最少也要 3 个字节来存储，对于基本只使用 ASCII 字符的人来说，要使用原来的 3 倍的存储空间来存储同等数量的字符那是很难接受的，毕竟那时候的存储空间本来就小，网络带宽也很小。那怎么办呢？于是大佬们又设计了好几种“存储方案”（我们将这些存储方案称为“字符编码”），常用的有 UTF-8、UTF-16、UTF-32。其中比较节省空间的就是 UTF-8、UTF-16 了，而 UTF-8 字符编码的前 128 个码点与 ASCII 字符编码的值相同，所以得到了互联网的广泛认可，建议大家都使用 UTF-8 编码。因为它完全兼容 ASCII 编码，合法的 ASCII 编码同时也是合法的 UTF-8 编码。

这里说明一下，我们通常将 Unicode 称为字符集，将 UTF-8、UTF-16 称为字符编码，因为 Unicode 字符集实在太大了，所以产生了好几种存储方案，但是 ASCII、Latin-1、GB2312、GBK 这些字符集只有一种编码方案（也就是码点是什么值就存入什么值），所以 ASCII 既是字符集名，也是字符编码名，同理，Latin-1、GB2312、GBK 这些也是。

• 字符 -> 数值：“编码”，encode，可以理解为将明文（字符）加密为密文（字节）；
• 数值 -> 字符：“解码”，decode，可以理解为将密文（字节）解密为明文（字符）。

很显然，如果想要让同一个字符在经过 encode、decode 后能保持一致，就必须保证 encode 和 decode 时的“算法”（其实就是字符编码）要一致，不然就是对牛弹琴，牛头不对马嘴，必然乱码。

所以乱码的根源就是 encode 时使用的字符编码和 decode 时使用的字符编码不一致导致的。OK，回到 MySQL 中来，字符编码的设置有两个地方，一个是 set names，一个是 column 的字符编码：

• SET NAMES 'charset_name' COLLATE 'collation_name'
• `column_name` char/varchar/text charset 'charset_name' collate 'collation_name'

为了好讲解，我将 set names 设置的字符编码称为“环境编码”，将 char/varchar/text 字段的字符编码称为“字段编码”（或者“数据编码”，因为字段是实际存储数据的地方）。先说说我自己的结论：

• 环境编码和数据编码可以不一样，比如一个为 utf8mb4，另一个为 gbk。
• 写入数据时、读取数据时，只要环境编码没有改动过，就不会出现乱码问题。
• 但是请不要给自己找麻烦，务必保证环境编码与字段编码一致，比如 utf8mb4。

记录长度限制

MySQL 中规定：任何一条记录最长不超过 65535 个字节，这意味着 varchar 永远达不到理论最大值。

那么，varchar 实际存储长度能达到多大呢？由编码字符集决定。下面，以 varchar 在 UTF-8 和 GBK 的情况为例，执行如下 SQL 语句，进行演示：

# 尝试创建 65535 个 utf-8 字符的 varchar，提示最多 21844 个
MariaDB [test]> create table t_utf8 (name varchar(65535)) default charset utf8;
ERROR 1074 (42000): Column length too big for column 'name' (max = 21844); use BLOB or TEXT instead
# 尝试创建 65535 个 gbk 字符的 varchar，提示最多 32766 个
MariaDB [test]> create table t_gbk (name varchar(65535)) default charset gbk;
ERROR 1074 (42000): Column length too big for column 'name' (max = 32766); use BLOB or TEXT instead

mysql 中的 utf8 码点最大值为 3 个字节，gbk 为 2 个字节，所以能够存储的 utf8 字符有 65535 / 3 = 21845 个，同理能存储的 gbk 字符为 65535 / 2 = 32767.5，即 32767 个。注意这只是理论值，还没有算 varchar 额外的 2 个用于记录数据长度的字节，所以能存储的 utf8 字符最多为 65533 / 3 = 21844 个，能存储的 gbk 字符最多为 65533 / 2 = 32766 个。我们来再试一次：

MariaDB [test]> create table t_utf8 (name varchar(21844)) default charset utf8;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> create table t_gbk (name varchar(32766)) default charset gbk;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> desc t_utf8;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| name  | varchar(21844) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.10 sec)
MariaDB [test]> desc t_gbk;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| name  | varchar(32766) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

如果你仔细计算了 utf8、gbk 的 varchar 的长度的话，你会发现它们都还剩余 1 个字节，那么我们再添加一个 tinyint 进去，正好一个字节长度：

# 报错，提示超过了 MySQL 最大的 row 大小 65535 字节，怎么回事？
# 因为在 MySQL 的记录中，如果有任何一个字段允许为 NULL，那么系统
# 就会自动从整个记录中保留一个字节来存储 NULL，若想释放 NULL 所占
# 的字节，则必须保证所有字段都不允许为 NULL。
MariaDB [test]> alter table t_utf8 add column age tinyint;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
# 将 age 声明为不可为 NULL 值，就可以了（居然还不可以，以后再说吧）
MariaDB [test]> alter table t_utf8 add column age tinyint not null;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
# 其实是因为 varchar 自身的可以为 NULL 占用了一个字节，所以要这样：
MariaDB [test]> create table tt_utf8 (name varchar(21844) not null, age tinyint not null) default charset utf8;
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> create table tt_gbk (name varchar(32766) not null, age tinyint not null) default charset gbk;
Query OK, 0 rows affected (0.02 sec)
MariaDB [test]> desc tt_utf8;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| name  | varchar(21844) | NO   |     | NULL    |       |
| age   | tinyint(4)     | NO   |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [test]> desc tt_gbk;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| name  | varchar(32766) | NO   |     | NULL    |       |
| age   | tinyint(4)     | NO   |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

字段属性

列属性
列属性：实际上，真正约束字段的是数据类型，但是数据类型的约束比较单一，因此就需要额外的一些约束来保证数据的有效性，这就是列属性。

列属性有很多，例如：null、not null、default、index、primary key、foreign key、unique key、auto_increment、comment 等。

空属性
空属性有两个值，分别为：null 和 not null。

虽然默认数据库的字段基本都为空，但是实际上在真正开发的时候，要尽可能的保证数据不为空，因为空数据没有意义，也没办法参与运算。而且 MySQL 很难对允许 NULL 值的列进行优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂，可为 NULL 的列也需要更多的存储空间。如果需要“空值”，可以使用 0、空串等特殊值表示。

列的属性默认为 null，除非你显式的声明 not null。例子：

MariaDB [test]> create table student (
    -> id int not null,
    -> name varchar(5) not null,
    -> grade float(5, 2) not null,
    -> profile varchar(30) null
    -> );
Query OK, 0 rows affected (0.42 sec)
MariaDB [test]> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| name    | varchar(5)  | NO   |     | NULL    |       |
| grade   | float(5,2)  | NO   |     | NULL    |       |
| profile | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

列描述
列描述：comment，表示描述（注释），没有实际含义，是专门用来描述字段的，其会随着表创建语句自动保存，用来给程序员（数据库管理员）了解数据库使用。

MariaDB [test]> create table student (
    -> id int not null comment '学号',
    -> name varchar(5) not null comment '姓名',
    -> age tinyint not null comment '年龄',
    -> score float(5, 2) not null comment '成绩'
    -> );
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> show full columns from student;
+-------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type       | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(11)    | NULL               | NO   |     | NULL    |       | select,insert,update,references | 学号    |
| name  | varchar(5) | utf8mb4_general_ci | NO   |     | NULL    |       | select,insert,update,references | 姓名    |
| age   | tinyint(4) | NULL               | NO   |     | NULL    |       | select,insert,update,references | 年龄    |
| score | float(5,2) | NULL               | NO   |     | NULL    |       | select,insert,update,references | 成绩    |
+-------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (0.01 sec)
MariaDB [test]> show create table student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                           |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL COMMENT '学号',
  `name` varchar(5) NOT NULL COMMENT '姓名',
  `age` tinyint(4) NOT NULL COMMENT '年龄',
  `score` float(5,2) NOT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4         |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)