mysql 8.0 解惑

S_zhangmin

已于 2022-03-16 21:29:35 修改

阅读量1.5k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： MySQL SQL 文章标签： mysql 数据库 database

于 2022-02-02 10:11:38 首次发布

本文链接：https://blog.youkuaiyun.com/S_zhangmin/article/details/122767980

MySQL 同时被 2 个专栏收录

25 篇文章

订阅专栏

SQL

18 篇文章

订阅专栏

本文详细介绍了MySQL中的数据类型、运算符、函数及查询技巧，包括如何使用特殊字符、存储文件、执行区分大小写的比较。此外，还涵盖了如何从日期时间值中提取部分信息、更改默认字符集、使用DISTINCT和ORDER BY。讨论了插入、更新和删除数据的注意事项，以及如何创建和使用索引。深入讲解了存储过程和函数、视图、触发器以及权限与安全性的管理。最后，探讨了数据库备份、日志管理和性能优化的相关策略，旨在帮助读者全面理解和优化MySQL数据库的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第五章数据类型和运算符

1. 在MySQL中如何使用特殊字符？

单引号（‘）、双引号（“）、反斜线（\）等在MySQL中是不能直接输入使用的，否则会产生意料之外的结果。在MySQL中，这些特殊字符称为转义字符，在输入时需要以反斜线应该输入（\\），其他特殊字符还有回车符（\r）、换行符（\n）、制表符（\tab）、退格符（\b）等。在向数据库中插入这些特殊字符时，一定要进行转义处理。

2. 在MySQL中可以存储文件吗？

MySQL中的BLOB和TEXT字段类型可以存储数据量较大的文件，比如存储图像、声音或者是大容量的文本内容（例如网页或者文档）。虽然使用BLOB或者TEXT可以存储大容量的数据，但是对这些字段的处理会降低数据库的性能。如果并非必要，可以选择只存储文件路径。

3. 在MySQL中如何执行区分大小写的字符串比较？

在windows平台下，MySQL是不区分大小写的，因此字符串比较函数也不区分大小写。如果想执行区分大小写的比较，可以在字符串前面添加BINARY关键字。例如，默认情况下，’a'=‘A'的返回结果为1，如果使用BINARY关键字，即BINARY ’a' = ‘A'，则结果为0。在区分大小写的情况下，’a'与‘A' 并不同。

第六章 MySQL函数

1. 如何从日期时间值中获取年、月、日等部分日期或时间值？

在MySQL中，日期时间值以字符串形式存储在数据表中，因此可以使用字符串函数分别截取日期时间之的不同部分，例如某个名称为dt的字段有值“2010-10-01 12：00：30”，如果只需要获得年值，可以输入LEFT(dt,4)，这样就获得了字符串左边开始长度为4的子字符串，即YEAR部分的值；如果要获取月份值，可以输入MID(dt,6,2),从字符串第6个字符开始长度为2的子字符串证号为dt中的月份值。同理，读者可以根据其他日期和时间的位置计算并获取相应的值。

2. 何如改变默认的字符集？

CONVERT()函数改变指定字符串的默认字符集。还有一个简单的方式，修改配置文件。在windows中MySQL配置文件名称为my.ini，该文件在MySQL的安装目录下面。修改配置文件中的default-character-set和character-set-server参数值，将其改为想要的字符集名称，如gbk、gb2312、latin1等，修改完之后重启MySQL服务，即可生效。再使用SHOW VARIABLES LIKE 'character_set_%'; 命令查看当前字符集。

第七章查询数据

1. DISTINCT可以应用于所有的列吗？

在查询结果中，如果需要对列进行降序排列，可以使用DESC，这个关键字只能对其面前的列进行降序排列。例如，要对多列进行降序排序，必须要在每一列的列名后面加DESC 关键字而DISTINCT不同，DISTINCT不能部分使用。换句话说，DISTINCT关键字应用于所有列而不仅时它后面的第一个指定列。例如，查询3个字段s_id、f_name、f_price，如果不同记录的这3个字段的组合值都不同，那么所有记录都会被查询出来。

2. ORDER BY可以和LIMIT混合使用吗？

在使用ORDER BY子句时，应保证其位于FROM子句之后，如果使用LIMIT，则必须位于ORDER BY之后，如果子句顺序不正确，MySQL将产生错误信息。

3. 什么时候使用引号？

在查询的时候，会看到在WHERE子句中使用条件，有的值加上了单引号，而有的值未加。单引号用来限定字符串，如果将值与字符串类型列进行比较，则需要限定引号；而用来与数值进来比较则不需要用引号。

4. 在WHERE子句中必须使用圆括号吗？

任何时候使用具有AND和OR操作符的WHERE子句都应该使用圆括号明确操作顺序。如果条件较多，即使能确实计算次序，默认的计算次序也可能会SQL语句不易理解，因此使用括号明确操作符的次序是一个好习惯。

5. 为什么使用通配符格式正确，却没有查找出符合条件的记录？

在MySQL中存储字符串数据时，可能会不小心把两端带由空格的字符串保存到记录中，而在查看表中记录时，MySQL不能明确地显示空格，数据库操作者不能直观地确定字符串两端是否由空格。例如，使用LIKE‘%e'匹配以字母e结尾的水果的名称，如果字母e后面多了一个空格，则LIKE语句不能将该记录查找出来。解决的方法是使用TRIM函数，将字符串两端的空格删除之后再进行匹配。

第八章插入、更新与删除数据

1. 插入记录时可以不指定字段名称吗？

不管使用哪种INSERT语法，都必须给出VALUES的正确数目。如果不提供字段名，就必须给每个字段提供一个值，否则将产生一条错误信息。如果要在INSERT操作中省略某些字段，那么这些字段需要满足一定条件：该列定义为允许空值；或者表定义时给出默认值，如果不给出值，将使用默认值。

2. 更新或者删除表时必须指定WHERE子句吗？

在前面的章节中可以看到，所有的UPDATE和DELETE语句全都在WHERE子句中指定了条件。如果省略WHERE子句，那么UPDATE或DELETE将被应用到表中所有的行。因此，除非确实打算或删除所有记录，否则要注意使用不带WHERE子句的UPDATE或DELETE语句。建议在对表进行更新和删除操作之前，使用SELECT语句确认需要删除的记录，以免造成无法挽回的结果。

第九章索引

1. 索引对数据库性能如此重要，应该如何使用它？

对数据库选择正确的索引是一项复杂的任务。如果索引列较少，则需要的磁盘空间和维护开销都比较少。如果在一个大表上创建了多种组合索引，索引文件也会膨胀很快。另一方面，索引较多可能覆盖更多的查询。可能需要实验若干不同的设计，才能找到最有效的索引。可以添加、修改和删除索引而不影响数据库架构或应用程序设计。因此，应尝试多了不同的索引从而建立最优的索引。

2. 尽量使用多索引。

对字符串类型的字段进行索引，如果可能，应该指定一个前缀长度。例如，有一个CHAR(255)的列，如果在前10个或30个字符内，多数值是唯一的，则不需要对整个列进行索引。短索引不仅可以提高查询速度，还可以节省磁盘空间、减少I/O操作。

第十章：存储过程和函数

1. MySQL存储过程和函数有什么区别？

本质上都是存储过程。函数只能通过return语句返回单个值或者表对象；而存储过程不允许执行return，但是可以通过out参数返回多个值。函数限制比较多，不能用临时表，只能用表变量，还有一些函数都不可用等；存储过程的限制相对比较少。函数可以嵌入SQL语句中使用，可以在SELECT语句中作为查询语句的一个部分调用；存储过程一般作为一个独立的部分来执行。

2. 存储过程中的代码可以改变吗？

可以

3. 在存储过程中可以调用其他存储过程吗？

可以在存储过程中可以使用CALL语句调用其他存储过程，但是不能使用DROP语句删除其他存储过程。

4. 存储过程的参数不要与数据表中的字段名相同。

定义存储过程参数列表时，注意把参数名与数据库表中的字段名区别开来，否则将出现无法预期的结果。

5. 存储过程的参数可以使用中文吗？

需要定义存储过程的时候在后面加上character set gbk，不然调用存储过程使用中文参数会出错，比如定义userInfo存储过程：

CREATE PROCEDURE userInfo(IN u_name VARCHAR(50) character set gbk,
OUT u_age INT
)

第十一章视图

MySQL中视图和表的区别以及联系是什么？

1. 两者的区别

1）视图是已经编译好的SQL语句，是基于SQL语句结果集的可视化表，而表不是。

2）视图没有实际的物理记录，而表有。

3）表是内容，视图是窗口。

4）表占用物理空间，而视图不占用物理空间。视图只是逻辑概念的存在，表可以及时修改，但视图只能用创建的语句来修改。

5）视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度来说，试图可以防止用户接触数据表，因而用户不知道表结构。

6）表属于全局模式种的表，是实表；视图属于局部模式的表，是虚表。

7）视图的建立和删除只影响视图本省，不影响对应的基本表。

2. 两者的联系

视图（view）是在基本表之上建立的表，它的结构（所定义的列）和内容（所有记录）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

第十二章触发器

1. 使用触发器时要特别注意的事项。

使用触发器的时候需要注意，对于相同的表，相同的事件只能创建一个触发器，比如对表account创建一个BEFORE INSERT 触发器，那么如果对表account再次创建一个BEFORE INSERT 触发器，MySQL将会报错，此时，只可以在表account上创建一个AFTER INSERT或者BEFORE UPDATE 类型的触发器。灵活地运用触发器将为操作省去很多麻烦。

2. 及时删除不再需要的触发器

触发器定义之后，每次执行触发条件事件都会激活触发器并执行触发器中的语句。如果需求发生变化，而触发器没有进行相应的改变或者删除，则触发器仍然会执行旧的语句，从而会影响新的数据完整性。因此，要将不再使用的触发器及时删除。

第十三章 MySQL权限与安全管理

1. 已经将一个账户的信息从数据库中完全删除，为什么该用户还能登录数据库？

出现这个情况的原因可能有多种，最有可能的是在user数据表中存在匿名帐户。在user表中匿名帐户的User字段置为空字符串，这会允许任何人连接到数据库。检测是否在匿名登录用户的方法是输入以下语句：

SELECT * FROM user WHERE User='';

如果有记录返回，则说明存在匿名用户，需要删除记录，以保证数据库的访问安全。

DELETE FROM user WHERE user='';

这样一来，该账户就肯定不能登录MySQL服务器了。

2. 应该使用哪种方法创建用户？

GRANT语句、CREATE USER语句和直接操作user表。一般情况下，最好使用GRANT或者CREATE USER语句，而不要直接将用户信息插入user表，因为user表中存储了全局级别的权限以及其它的账户信息，如果意外破坏了user表中的记录，则可能会对MySQL服务器造成很大影响。

第十四章数据备份与恢复

1. MySQLdump备份的文件只能在MySQL中使用吗？

MySQLdump备份的文件实际是数据库的一个副本，使用该文件不但可以在MySQL中恢复数据库，而且通过对文件的简单修改，可以使用该文件在SQL Server或者Sybase等其他数据库中恢复数据库。这在某种程度上实现数据库之间的迁移。

2. 如何选择备份工具？

直接复制数据文件是最为直接、快速的备份方法，缺点是基本上不能实现增量备份。备份时必须确保没有使用这些表。如果再复制一个表的同时服务器正在修改它，则复制无效。备份文件时，最好关闭服务器，然后重启服务器。为了保证数据的一致性，需要在备份文件前执行以下SQL语句：

FLUSH TABLES WITH READ LOCK;

也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证复制过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单，直接复制回原来的数据库目录下即可。

MySQLhotcopy是一个PERL程序，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。他是备份数据库或单个表最快的途径，但它只能运行在数据库文件所在的机器上，并且MySQLhotcopy只能用于备份MyISAM表。MySQLhotcopy适用于小型数据库的备份，数据量不大，可以使用MySQLhotcopy程序每天进行一次完全备份。

MySQLdump将数据表导成SQL脚本文件，在不同的MySQL版本之间升级时相对比较合适，这也是最常用的备份方法。MySQLdump比直接复制要慢些。

3. 使用MySQLdump备份整个数据库成功，把表和数据库都删除了，但使用备份文件却不能恢复数据库。

出现这种情况，是因为备份的时候没有指定--databases参数。默认情况下，如果只指定数据库名称，MySQLdump备份的是数据库中所有的表，而不包括数据库的创建语句。

mysqldump -u root -p booksDB > c:\booksDB.sql

该语句只备份了booksDB数据库下所有的表，读者打开该文件，可以看到文件中不包含创建booksDB数据库的CREATE DATABASE语句，因此如果把booksDB也删除了，使用该sql文件不能恢复以前的表，恢复时会出现ERROR 1046（3D000）：NO database selected的错误信息。必须在MySQL命令行下创建booksDB数据库，并使用use语句选择booksDB之后才可以恢复。

mysqldump -u root -p --databases booksDB > C:\books_DB.sql

该语句不仅备份了所有数据库下的表结构，还包括创建数据库的语句。

第十五章 MySQL日志

1. 平时应该打开哪些日志？

日志即会影响MySQL的性能，又会占用大量磁盘空间。因此，如果不必要，应尽可能少地开启日志。根据不同的使用环境，可以考虑开启不同的日志。例如，在开发环境中优化查询效率低的语句，可以开启慢查询日志；如果需要记录用户的所有查询操作，可以开启通用查询日志；如果需要记录数据的变更，可以开启二进制日志；错误日志是默认开启的。

2. 如何使用二进制日志？

二进制日志主要用来记录数据变更。如果需要记录数据库的变化，可以开启二进制日志。基于二进制日志的特性，不仅可以用来经行数据恢复，还可用于数据复制。在数据库定期备份的情况下，如果出现数据丢失，可以先用备份恢复大部分数据，然后使用二进制日志恢复最近备份后变更的数据。在双机热备情况下，可以使用MySQL的二进制日志记录数据的变更，然后将变更部分复制到备份服务器上。

3. 如何使用慢查询日志？

慢查询日志主要用来记录查询时间较长的日志。在开发环境下，可以开启慢查询日志来记录查询时间较长的查询语句，然后对这些语句进行优化。通过配置long_query_time的值，可以灵活地掌握不同程度的慢查询语句。

PART16 性能优化

1. 是不是索引建立得越多越好？

合理的索引可以提高查询的速度，但不是索引越多越好。在执行插入语句的时候，MySQL要为新插入的记录建立索引。所以过多的索引会导致插入操作变慢。原则上是只有查询用的字段才建立索引。

2. 为什么查询语句中的索引没有起作用？

在where条件的LIKE关键字匹配的字符串以”%“开头。
WHERE条件中使用OR关键字连接查询条件，如果有一个字段没有使用索引，那么其他索引也不会起作用。
如果使用多列索引，但没有使用多列索引中的第1个字段，那么多列索引也不会起作用。

3.如何使用查询缓冲区？

可以修改query_cache_size以调整查询缓冲区大小，修改query_cache_type以调整查询缓冲区的类型。在my.ini中修改query_cache_size和query_cache_type的值：

[mysqld]
query_cache_size=512M
query_cache_type=1

query_cache_type=1表示开启查询缓冲区。只有在查询语句中包含SQL_NO_CACHE关键字时，才不会使用查询缓冲区。可以使用FLUSH QUERY CACHE语句来刷新缓冲区，清理查询缓冲区中的碎片。

-- end