【MySQL】库的相关操作
目录
- 1. 库的创建
- 1.1 创建语法
- 1.2 编码问题
- 1.3 查看系统默认的字符集和校验集
- 1.4 查看数据库的字符集和校验集
- 1.2 指定编码规则创建数据库
- 2. 库的删改查
- 2.1 数据库的删除
- 2.2 选择数据库
- 2.3 查看自己所处的数据库
- 2.4 修改字符集或者校验集
- 3. 库的备份与恢复
- 3.1 备份
- 3.2 恢复
- 3.3 注意事项
- 4. 查看库的使用情况
1. 库的创建
1.1 创建语法
创建语法：
mysql> create database xxx;
还可以带选项，比如if not exists表示没有才创建：
mysql> create database if not exists xxx;
1.2 编码问题
数据库常见的有两个编码集，一个是字符集，另一个是校验集也叫做排序规则（校验规则），因为不光是数据库，这类一旦涉及到数据的表示或者数据的存取操作那就必然涉及数据的编码格式的问题，只有使用相同的编码格式来操作数据才不会导致数据乱码问题或者处理数据错误
很明显的道理，用utf-8编码格式存储数据然后用ascii编码格式读取，那必然会导致乱码，因为ascii编码是utf-8编码的一个子集，仅支持英文字符和一些控制字符，而utf-8编码则支持全球几乎所有语言的字符，包括那些ascii无法表示的字符（如中文、日文、特殊符号等），所以要保证数据的编码格式一致
换个例子，用中文编写的文章那就只能由懂中文的来读，否则读不懂
字符集就是未来数据库存储数据的格式，定义了数据库中可以存储哪些字符，以及这些字符的编码格式，而校验集就是数据库对数据进行增删查改等这类处理操作时所采用的编码格式，总之，数据库对数据的所有操作，都必须保证使用与字符集和校验集相匹配的编码方式
1.3 查看系统默认的字符集和校验集
列出系统所有的字符集：
mysql> 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 | utf8mb3 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
character_set_client：客户端发送到服务器的语句的字符集。
character_set_connection：客户端与服务器之间通信时使用的字符集。
character_set_database：当前选定数据库的默认字符集（如果服务器支持，则为每个数据库指定）。
character_set_filesystem：MySQL 用来在文件系统中创建新表或数据库时使用的字符集。binary 表示不使用字符集转换，直接以二进制形式存储。
character_set_results：服务器返回给客户端的结果集的字符集。
character_set_server：服务器的默认字符集（如果服务器支持，则为每个连接指定）。
character_set_system：系统元数据的字符集（如系统表中的数据）。这是 MySQL 内部使用的字符集，通常不需要用户更改。
character_sets_dir：包含 MySQL 支持的字符集文件的目录。
utf8mb4 是一种字符集，它是 utf8 的超集，支持更多的Unicode字符，特别是完全支持了 Unicode 字符集中的所有字符，包括 emoji 表情符号等。与 utf8 相比，utf8mb4 使用最多4个字节来存储每个字符，而 utf8 最多使用3个字节，这限制了 utf8 能够表示的字符范围
列出所有的校验集：
mysql> show variables like "collation_%";
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
collation_connection：这是当前连接使用的排序规则。当客户端与服务器建立连接时，如果客户端没有指定排序规则，则会使用此设置。
collation_database：这是当前选定数据库（如果没有指定，则可能是默认数据库）的默认排序规则。如果在创建表时没有指定排序规则，则会使用数据库的默认排序规则。
collation_server：这是服务器的默认排序规则。如果其他排序规则变量（如 collation_connection 或 collation_database）没有为当前操作指定排序规则，则会回退到服务器的默认排序规则。
utf8mb4_0900_ai_ci 是一个具体的排序规则，它包含几个关键部分：
utf8mb4：这是字符集，表示使用 UTF-8 编码，且支持最多 4 个字节的字符（即包括 Unicode 的所有字符）。
0900：这是 Unicode 排序算法（Unicode Collation Algorithm, UCA）的版本号。版本号越高，排序规则通常越准确和符合现代语言习惯。0900 是一个相对较新的版本，提供了更准确的排序和比较。
ai：表示“大小写不敏感”（accent insensitive）。这意味着在比较字符串时，不会考虑字母的大小写差异。但是，请注意这里的“ai”实际上是指对重音不敏感（accent insensitive），但在 MySQL 的上下文中，它也常被用来表示大小写不敏感，尽管技术上可能有些混淆。MySQL 的排序规则通常将大小写不敏感与重音不敏感结合在一起。
ci：表示“大小写不敏感”（case insensitive）。这是明确指定大小写不敏感的标志，但在 utf8mb4_0900_ai_ci 这样的命名中，ai 已经隐含了大小写不敏感的含义（尽管如前所述，这里的“ai”主要指的是重音不敏感）。不过，ci 的存在是为了与只考虑大小写的排序规则（如 utf8mb4_0900_bin，其中 bin 表示二进制比较，即大小写敏感）进行区分。
查看当前数据库所支持的字符集：
mysql> show charset;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| utf8mb3 | UTF-8 Unicode | utf8mb3_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+----------+---------------------------------+---------------------+--------+
也可以查看数据库所支持的校验集：
mysql> show collation;
说白了，字符集就是数据按照什么方式去存放，而校验集则是把数据按照什么方式取出来进行解释，所以要想保证数据一致就需要保证二者编码一致
系统和数据库的字符集和校验集默认使用的是utf8mb4，是符合要求的，不需要特别改动
1.4 查看数据库的字符集和校验集
使用命令：show create database xxx;：
mysql> show create database db1;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以发现db1这个数据库使用默认的字符集和校验集utf8mb4和utf8mb4_0900_ai_ci
MySQL建议关键字使用大写，但只是建议
创建数据库。名字要带反引号’'，这是为了防止与关键字命名冲突
最后/**/的意思是，如果当前MySQL版本大于4.01则执行这条指令
上面这种方式显示的结果可能不显示默认校验规则，也是可以使用下述方法，直接查询information_schema数据库中的schemata表：
mysql> select default_character_set_name, default_collation_name -> from information_schema.schemata-> where schema_name='db1';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4 | utf8mb4_0900_ai_ci |
+----------------------------+------------------------+
1 row in set (0.00 sec)
早期MySQL版本使用MyISAM为默认存储引擎，因此一个数据库使用的字符集和校验集信息是保存在一个后缀.opt的文件中的，而现在最新版本使用innoDB为默认存储引擎，在该引擎下，数据库和表的结构信息主要存储在系统表中而不是生成类似.opt的文件
1.2 指定编码规则创建数据库
指定字符集，有两种方式：
# 第一种
mysql> create database db2 charset=gbk;
Query OK, 1 row affected (0.03 sec)# 第二种
mysql> create database db3 character set gbk;
Query OK, 1 row affected (0.02 sec)
同时指定校验集，并查看：
mysql> create database db4 charset=gbk collate=gbk_chinese_ci;
Query OK, 1 row affected (0.02 sec)mysql> select default_character_set_name, default_collation_name from information_schema.schemata where schema_name='db4';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| gbk | gbk_chinese_ci |
+----------------------------+------------------------+
1 row in set (0.00 sec)
也可以指定校验集而不制定字符集，这样字符集会使用系统默认的，因此若指定的校验集与默认字符集不匹配，也就是存和取的格式不一致，则会可能会出现数据不一致，除此之外，使用相同的字符集但校验集不同的数据库，在进行相同的查询时得到的结果也大概率是不一样的
如果用户指定了某种编码格式则会按照用户指定的创建，否则会按照默认配置文件中指定的编码，因此一般是不需要用户指定的，使用默认即可
2. 库的删改查
2.1 数据库的删除
删除语法：
mysql> drop database xxx;
还可以带选项，比如if exists表示存在才删除：
mysql> drop database if exists xxx;
Query OK, 0 rows affected (0.02 sec)
2.2 选择数据库
使用use xxx指令，进入某数据库：
mysql> use hello_world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
2.3 查看自己所处的数据库
通过database()函数查看，如果不在任何一个数据库中则显示：
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
否则显示：
mysql> select database();
+-------------+
| database() |
+-------------+
| hello_world |
+-------------+
1 row in set (0.00 sec)
2.4 修改字符集或者校验集
修改前db1使用的字符集和校验集为默认：
mysql> show create database db1;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
修改后：
mysql> alter database db1 charset=gbk collate=gbk_chinese_ci;
Query OK, 1 row affected (0.01 sec)mysql> show create database db1;
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3. 库的备份与恢复
3.1 备份
有一种比较简单粗暴的做法就是类似在Linux下保存目录一样，把整个数据库打包一份，因为数据库本质也是一个目录，但严重不推荐，因为在恢复时可能出现问题，比如当版本不匹配会导致恢复的数据库和原始数据不符合，又或者没有打包依赖项而导致数据库没法正常运行等
这种做法叫做冷备份或者物理备份，不适用于所有情况
另外一种比较推荐的做法是使用MySQL所自带的一个备份工具mysqldump，语法如下：
root@VM-16-3-ubuntu:~/MySQL# mysqldump -P 3306 -u root -p -B hello_world > hello_world_cp.sql
Enter password:
选项说明：-B，如果不带在恢复时需要先创建一个空数据库，然后在恢复
所以要实现重命名的话，可以不带，然后创建数据库的时候重新设置
最后重定向到目标路径下，备份成功后会在该路径下创建一个文件，该文件中保存的就是历史上对这个数据库的所有有效sql操作，所以恢复本质是再重新执行一遍这些操作
除此之外还可以备份某个数据库中的某些表或者备份多个数据库等
3.2 恢复
语法如下：
mysql> source /root/MySQL/hello_world_cp.sql;
这样就完成了一个数据块的恢复
3.3 注意事项
不推荐轻易对数据库进行删除或者重命名，因为有很多人可能在同时使用这个数据库，而且上层应用都是只认库的名字，一旦删除或重命名，应用可能就跑不起来了
4. 查看库的使用情况
如果发现操作比较卡顿，可以查看有哪些用户在使用当前数据库：
mysql> show processlist;
+----+-----------------+-----------+--------+---------+--------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------+---------+--------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 947870 | Waiting on empty queue | NULL |
| 65 | root | localhost | basecp | Query | 0 | init | show processlist |
+----+-----------------+-----------+--------+---------+--------+------------------------+------------------+
2 rows in set, 1 warning (0.00 sec)