Mysql学习笔记，持续记录

共计 13993 个字符，预计需要花费 35 分钟才能阅读完成。

找回 Mysql 的登录密码

在使用 CentOS 系统中，也许你会对很多的东西进行设置密码，来保护你的电脑的安全问题等，那么，如过一个不小心把密码忘记了，也许会给你的工作带来很多的不便。下面我们就来帮大家解决一个关于 CentOS 系统中 mysql 登录密码的问题。

CentOS 系统中那么忘记了 mysql 的登录密码, 怎么办? 使用安全模式吧.

首先需要在 CentOS 系统中停止 mysql:

$ service mysqld stop #停止数据库
# /usr/bin/mysqld_safe --skip-grant-tables & #后台守护进程

下面在 CentOS 系统继续输入:

msql> mysql -u root -p

下面的密码直接键入回车即可.

下面既是在 CentOS 系统重新设置密码:

mysql> update mysql.user set password=password("新密码") where user="root";
mysql> update user set authentication_string = password('123456') where user = 'root';;   #mysql 5.7
#刷新权限
$ mysql>flush privileges;
# 退出
mysql>exit;
# 启动 mysql, 继续以往的登录动作即可.
/rc.d/init.d/mysqld start
mysql -u root -p

这样，我们就应该可以在 CentOS 系统不怕丢失 mysql 的登录密码了。希望大家可以一样的学习好 CentOS 系统。

通过 Mysql 二进制日志找回数据

./mysqlbinlog /usr/local/mysql/var/mysql-bin.000308 >> "3.txt"

Mysql 日志相关操作：https://segmentfault.com/a/1190000003072237

批量生成修改表和字段字符集的 SQL

SELECT
    CONCAT(
        'ALTER TABLE `',
        TABLE_NAME,
        '` MODIFY `',
        COLUMN_NAME,
        '`',
        DATA_TYPE,
        '(',
        CHARACTER_MAXIMUM_LENGTH,
        ') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
        (
            CASE
            WHEN IS_NULLABLE = 'NO' THEN
                'NOT NULL'
            ELSE
                ''
            END
        ),
        ';'
    )
FROM
    information_schema. COLUMNS
WHERE
    TABLE_SCHEMA = 'dataBaseName'
AND (DATA_TYPE = 'varchar' OR DATA_TYPE = 'char')

SELECT
    CONCAT(
        'ALTER TABLE',
        TABLE_NAME,
        'CONVERT TO CHARACTER SET utf8mb4;'
    )
FROM
    information_schema. TABLES
WHERE
    TABLE_SCHEMA = 'dataBaseName';

dataBaseName 修改为指定数据库的名字。

mysql 设置 utf8_mb4

首先将数据库、数据表、表字段全部设置成 utf8_mb4

然后修改 mysql 配置文件

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

重启 Mysql，完事

查询某个表的所有外键

select * from information_schema.key_column_usage where REFERENCED_TABLE_NAME='qd_user' and table_name='qd_order'

Group By

1. 使用规定

group by 子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
如果在 group by 子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
group by 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 select 中使用表达式，则必须在 group by 子句中指定相同的表达式。不能使用别名。
除聚集计算语句外，select 语句中的每个列都必须在 group by 子句中给出。
如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
group by 子句必须出现在 where 子句之后，order by 子句之前。

提示

使用 with rollup 关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值（也就是会将 NULL 的分组列出来），如下所示：

explain

1. 作用

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

2. 语法

explain select ...options

3. 输出结果

+----+-------------+-------------------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | nicen_mini_upload | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 148160 |   100.00 | NULL  |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+--------+----------+-------+

4. 解释

id

id 代表 select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序，有以下三种情况：

id 相同，执行顺序由上至下
id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
id 相同不同，同时存在，从大到小，从上到下执行；

select_type

分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

SIMPLE：简单的 SELECT 查询，不包含子查询或 UNION 操作。
PRIMARY：在查询中最外层的 SELECT 操作。
SUBQUERY：在 SELECT 列表或 WHERE 子句中使用的子查询。
DERIVED：从 SELECT 语句的 FROM 子句中派生出的临时表，例如 FROM 子句中使用的子查询或 UNION 操作。
UNION：UNION 中的第二个或后续 SELECT 操作。
DEPENDENT UNION：UNION 中的第二个或后续 SELECT 操作，依赖于外部查询的值。
UNION RESULT：UNION 操作的结果集。
DEPENDENT SUBQUERY：在 SELECT 列表中的子查询，依赖于外部查询的值。
DEPENDENT UNION RESULT：UNION 操作的结果集，依赖于外部查询的值。

table

table 指的就是当前执行的表

type

type 所显示的是查询使用了哪种类型，type 包含的类型包括如下图所示的几种：

NULL：MySQL 尚未确定表的类型，通常是由于查询中包含了不同类型的连接，或者查询在 FROM 子句中不包含任何表。
const：MySQL 通过索引或主键在一行中查找唯一的匹配行，例如使用 WHERE 子句和主键 ID = 1。
eq_ref：MySQL 使用连接条件在不同的表之间匹配单个行，例如使用连接条件在 orders 表和 order_details 表之间进行匹配。
ref：MySQL 使用连接条件从前面的表中选择所有匹配的行，并使用索引从其余的表中选择行。这个类型比 SYS 更好，因为它扫描匹配索引中的笛卡尔积而不是全表扫描。
range：MySQL 使用索引查找表中满足范围条件的行，例如使用 WHERE 子句包含 BETWEEN，IN()或 <,> 运算符。
index：MySQL 使用索引扫描整个索引中的所有行，该类型与 ALL 类型相似，只不过它扫描的是索引而不是表本身。
ALL：MySQL 需要扫描全表，即使存在索引也如此，例如没有匹配索引的 WHERE 子句使用 LIKE 操作符或使用不同数据类型进行比较。

注意

一般保证查询至少达到 range 级别，最好能达到 ref。

possible_keys

possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

实际使用的索引，如果为 NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）, 查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在 key 列表中。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的。

ref

显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

Extra

包含不适合在其他列中显式但十分重要的额外信息

Using index：MySQL 在查询语句中使用了覆盖索引，从索引中读取所有必要的列，而无需访问表本身。
Using where：MySQL 在查询语句中使用了 WHERE 子句进行过滤，但没有使用索引来加速数据筛选，通常表示需要优化查询。
Using temporary：MySQL 使用了临时表来支持查询，该临时表存储了查询中的某些中间结果，通常意味着需要进行查询优化。
Using filesort：MySQL 使用文件排序对结果进行排序，这意味着 MySQL 需要从内存中读取结果集并将其保存到临时文件中，然后再从该文件中排序。
Range checked for each record：MySQL 在执行索引扫描时使用了 WHERE 子句，并且将检查每个满足条件的条目，以确保它符合其他限制条件。
Using join buffer：MySQL 使用了连接缓存，以存储使用表和表之间的关联查询的中间结果，以避免在每个循环中重复读取。
Impossible where：MySQL 分析了查询并发现 WHERE 子句中包含明显错误的限制条件，因此返回任何行都是不可能的。
Select tables optimized away：MySQL 优化了查询，并发现所有查询所需的数据都可以从现有索引中获取，因此不需要访问表本身。

Analyze

1. 作用

ANALYZE TABLE 分析后的统计结果会反应到 cardinality 的值，该值统计了表中某一键所在的列，不重复的值的个数。该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使用。也就是索引列的 cardinality 的值与表中数据的总条数差距越大，即使查询的时候使用了该索引作为查询条件，实际存储引擎实际查询的时候使用的概率就越小。我们都知道，索引尽量建立在重复值很少的列上就是基于这个原因。

ANALYZE TABLE 会统计索引分布信息，并将结果持久化存储；
对于 MyISAM 表，相当于执行了一次 myisamchk –analyze；
支持 InnoDB、NDB、MyISAM 等存储引擎，但不支持视图（view）；
ANALYZE TABLE 也可以用在表分区上；
对 InnoDB、MyISAM 表执行 ANALYZE TABLE 时，会加上读锁（read lock）；
执行 ANALYZE TABLE 会记录 binlog。（这是合理的，因为索引分析这个操作，在 MASTER 端执行完后，SLAVE 端也是需要的）

cardinality 可以通过 SHOW INDEX FROM 表名查看：

mysql> show index from nicen_mini_upload;
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| nicen_mini_upload |          0 | PRIMARY  |            1 | id          | A         |      168369 | NULL     | NULL   |      | BTREE      |         |               |
| nicen_mini_upload |          1 | 1        |            1 | box         | A         |        4575 | NULL     | NULL   |      | BTREE      |         |               |
| nicen_mini_upload |          1 | 2        |            1 | user_id     | A         |       29595 | NULL     | NULL   |      | BTREE      |         |               |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

按列去重

mysql 按照某一字段去重，并显示其他字段信息。

select * from logtest
where id in (select Max(id) from logtest group by msg)
order by create_time asc limit 3;

mysql 索引失效的场景

模糊查询，例如使用 LIKE 和 NOT LIKE 操作符进行查询，如果查询模式以通配符 % 开头，MySQL 就会放弃使用索引，需要执行全表扫描，导致索引失效。
在查询中使用函数或计算后的列，例如 SELECT id FROM table WHERE YEAR(add_time) = 2021 在使用 YEAR 函数后，MySQL 就无法使用索引进行查询，需要执行全表扫描。
排序字段没有建立索引，如果没有对排序字段进行索引，MySQL 在对查询结果进行排序时将执行表扫描，导致索引失效。
数据表中的列过多，导致多个索引占用空间过大，MyISAM 引擎对于一次查询只能使用一个索引，如果后面的查询需要用到另外一个索引，就需要重新扫描表。
数据表中的数据量过大，为了定位具体的数据行，MySQL 需要进行索引的二次查询，就需要更多的 I / O 操作。
使用 NOT IN、<>、!= 等操作符的查询，这些查询需要进行全表扫描来定位符合条件的行，因此无法利用索引来优化查询。
数据类型不匹配，例如将一个整数转换为字符串类型，MySQL 无法使用索引来优化查询，在执行查询时需要强制进行数据类型转换。
数据表中存在大量重复的值，MySQL 优化器可能会放弃使用索引，转而执行全表扫描。
查询条件中使用太多的 OR 语句。如果查询中使用多个 OR 操作符并且没有使用括号将它们分组，则 MySQL 无法使用索引来优化查询。
对于包含多个列的索引，如果查询条件中只使用了索引的一部分列，则 MySQL 无法使用完整的索引来优化查询，导致索引失效。
对于多表查询，如果连接条件没有使用索引，则 MySQL 需要对整个表执行全表扫描来找到匹配条件的行，导致索引失效。
如果多个查询同时占用相同的索引，由于 MySQL 一次只能使用一个索引，查询可能会相互竞争，从而导致索引失效。
如果使用了字符串作为数据表的主键，由于磁盘空间和内存限制，MySQL 可能会选择使用哈希索引，而不是 B -tree 索引，哈希索引无法支持范围查询和排序操作，如果执行这些操作，会导致索引失效。
如果数据表使用了 MySQL 的自动分区特性，当查询涉及到多个分区时，MySQL 无法同时使用多个索引来优化查询，这可能导致索引失效。
当对一个非常大的数据表进行查询时，MySQL 可能会使用临时表来处理查询结果，如果临时表无法使用索引，则会导致索引失效。
NULL 值查询。在使用 IS NULL 或 IS NOT NULL 操作符进行查询时，MySQL 无法使用索引来优化查询。这是因为 MySQL 不会在索引中存储 NULL 值。
使用多个 JOIN 操作进行多表查询时，如果 ON 条件中的列没有建立索引，则 MySQL 无法使用索引来优化查询。
如果执行一个复杂的查询，例如子查询或联合查询，则 MySQL 可能无法使用索引来优化查询。
如果数据表中的列经常进行更新操作，特别是在 InnoDB 引擎中，MySQL 可能无法有效地使用索引来优化查询。
如果数据表使用 MySQL 的空间数据类型（例如 Point 或 LineString），则 MySQL 无法使用普通的 B -tree 索引来优化查询，需要使用特殊的空间索引。
如果数据表中使用了大对象（例如 BLOB 或 TEXT 类型），由于这些数据会占用大量的磁盘空间，MySQL 可能无法使用索引来优化查询。
使用 SELECT * 查询。如果您使用 SELECT * 从数据表中检索所有列，则 MySQL 可能无法使用索引来优化查询。这是因为 MySQL 需要检索所有列的值，从而导致索引失效。
使用 LIKE 查询。如果查询条件中使用 LIKE 操作符进行模糊查询，而没有使用通配符前缀，那么 MySQL 可以使用索引来优化查询。但是，如果使用了通配符前缀（例如 %abc）或者不在字符串开头使用通配符（例如 abc%def），则 MySQL 无法使用索引来优化查询。
查询优化器错误。MySQL 的查询优化器可能会在某些情况下错误地选择全表扫描而不是使用索引。这可能会导致索引失效。
聚合函数。如果在查询中使用了聚合函数（例如 COUNT、SUM 或 AVG），则 MySQL 可能无法使用索引来优化查询。这是因为聚合函数需要处理所有符合查询条件的行，而不是单独处理某个索引。
内存不足。如果查询需要处理大量数据，并且服务器的内存不足，则 MySQL 可能会选择使用磁盘临时表，从而导致索引失效。
使用函数。如果查询条件中使用了函数，例如 DATE()或 YEAR()，则 MySQL 无法使用索引来优化查询。这是因为查询需要在索引中执行函数而不是在存储的值上执行函数，从而导致索引失效。

问题集锦

1. 空判断

空值也就是在字段中存储 NULL 值，空字符串就是字段中存储空字符(’’)。所以查询某个字段为空的所有数据，只能使用 is null 判断符。

2. null 和数值进行比较

MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。这个“任何值”包括 NULL 本身！这就是为什么 MySQL 提供 IS NULL 和 IS NOT NULL 两种操作来对 NULL 特殊判断。

使用 null 和任何数值去做比较判断，得到的都是 false；只能使用 is null 和 is not null 进行操作；

3. datetime 的使用

mysql 进行日期比较时，日期格式必须是标准的 YYYY-MM-DD，小于 10 的日期需要加 0；

在 sql 语句中进行时间比较的时候，时间值也需要和字符串一样使用单、双引号包括。所有 datetime 的值是可以同字符串一样进行拼接的

/* 按时间筛选 */
if (!empty($json['remain']) || ($json['remain'] === "0")) {$now = date("Y-m-", time()); // 当天的日期
    $time = time() + (intval($json['remain'])*3600 * 24);
    $condition[] = '(concat("' . $now . '", `repayment`)) <="' . date("Y-m-d", $time) . '"';
}

DATEDIFF(start，end) 函数返回两个日期之间的天数。start-end 的差值；

4. FIND_IN_SET

FIND_IN_SET(str,strlist)

str 要查询的字符串
strlist 字段名参数以”,”分隔如 (1,2,6,8,10,22)
查询字段 (strlist) 中包含 (str) 的结果，返回结果为 null 或记录
假如字符串 str 在由 N 个子链组成的字符串列表 strlist 中，则返回值的范围在 1 到 N 之间。一个字符串列表就是一个由一些被‘,'符号分开的子链组成的字符串。如果第一个参数是一个常数字符串，而第二个是 type SET 列，则 FIND_IN_SET() 函数被优化，使用比特计算。如果 str 不在 strlist 或 strlist 为空字符串，则返回值为 0。如任意一个参数为 NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号 (‘,') 时将无法正常运行。

5. 包含于匹配

like 匹配时，可以用 instr 函数方案代替，效率的话还得自己测一测。

6. 插入失败时，主键也会自增

不管是显式还是隐式开启事务，执行成功与否，主键 id 都会自增 1

7. 外键创建失败的几种原因

外键的字段与关联的字段的类型不匹配（包括编码格式的不匹配）
外键的名字是一个已经存在的键值，要保证名字的唯一
mysql 引擎引起的外键创建不能保存或者不能发挥作用的情况，mysql 一般的默认引擎是 myisam，而 myisam 是不能创建外键的。
试图创建的一个外键没有建立起索引，或者不是一个 primary key 并且如果其中不是一个 primary key, 你必须为他创建一个索引。
外键的动作设置成 on delete set null 或者 on update set null，但是在关联的表的字段又设置的 no null，导致冲突。
在 navicat 设定的表格主键中有主键 1，主键 2，主键 3，的区别，主键 1 不能被级联更新（删除）（CASCADE）
外键存在默认值，引起的冲突
混合键值缺少索引引起的情况，需要为他单独申请索引
在 sql 语句创建外键时，create 或者 alter 语法错误

8. 按照指定值对结果集排序

select * from ta order by field(name,'seiki','iris','xut');

9. 列中使用子查询

( SELECT
	a.id,
	a.NAME,
	a.card_id,
	a.total,
	a.datetime,
	(SELECT count(*) FROM wx_users where card_id = a.card_id ) As count
	FROM
		`wx_cards` `a`
		INNER JOIN `wx_cards` `c` ON `a`.`card_id` = `c`.`card_id` 

	ORDER BY
		`a`.`datetime` DESC 
	)

10. 多列查询

(select GROUP_CONCAT(name) As permission,route from mv_permiss where route = 2 GROUP BY route) As b

concat，将多个字符串连接成一个字符串。concat(str1, str2,…)
concat_ws，和 concat() 一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符
group_concat，GROUP_CONCAT(c.course_name SEPARATOR '|')

11. 允许外部 IP 登录

# use mysql;
# update user set host = '%' where user = 'root';
# flush privileges;

mysql 导出、导入数据

1. 执行 sql 文件报错

导出一切正常，导入的时候报错了，查了一下，说是严格模式的锅。执行命令：

mysql> select @@sql_mode;
+-------+
| @@sql_mode                                                                                                                               
+-------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------+
1 row in set (0.02 sec)

ONLY_FULL_GROUP_BY：对于 GROUP BY 聚合操作，如果在 SELECT 中的列，没有在 GROUP BY 中出现，那么将认为这个 SQL 是不合法的，因为列不在 GROUP BY 从句中
STRICT_TRANS_TABLES：严格模式，在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制
NO_ZERO_IN_DATE：在严格模式，不接受月或日部分为 0 的日期。如果使用 IGNORE 选项，我们为类似的日期插入’0000-00-00’。在非严格模式，可以接受该日期，但会生成警告。（注：但可以插入“0000-00-00 和 0000-01-01”）
NO_ZERO_DATE：在严格模式，不要将 '0000-00-00’做为合法日期。你仍然可以用 IGNORE 选项插入零日期。在非严格模式，可以接受该日期，但会生成警告（注：年月日中任意一个不为零都可插入，全为 0 报错）
ERROR_FOR_DIVISION_BY_ZERO：在严格模式，在 INSERT 或 UPDATE 过程中，如果被零除(或 MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时 MySQL 返回 NULL。如果用到 INSERT IGNORE 或 UPDATE IGNORE 中，MySQL 生成被零除警告，但操作结果为 NULL。
NO_AUTO_CREATE_USER：防止 GRANT 自动创建新用户，除非还指定了密码。
NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

所以需要修改一下这个模式，然后重新导入：

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

重新导入，一切 OK；

2. 导入、导出 sql 文件

# 登录数据库
mysql -u -p 指定数据库

# 关闭外键
SET FOREIGN_KEY_CHECKS = 0;
source sql 文件.sql
# 等待执行完毕即可，window 系统导入时 SQL 和 Mysql 需要在一个盘符

# window 可以 cd 到 sql 文件的路径，使用相对路径导入

# 导入 SQL 文件
mysql -u username -p database_name < file.sql --force

# 导出数据库
mysqldump -u 用户名 -p 数据库名 [数据表] > 导出的文件名

--opt 如果有这个参数表示同时激活了 mysqldump 命令的 quick，add-drop-table，add-locks，extended-insert，lock-tables 参数，它可以给出很快的转储操作并产生一个可以很快装入 MySQL 服务器的转储文件。当备份大表时，这个参数可以防止占用过多内存。（反之使用 --skip-opt）
--single-transaction 设置事务的隔离级别为可重复读，然后备份的时候开启事务，这样能保证在一个事务中所有相同的查询读取到同样的数据。注意，这个参数只对支持事务的引擎有效，如果有 MyISAM 的数据表，并不能保证数据一致性。(自动关闭选项 --lock-tables)
--default-character-set=charset 指定转储数据时采用何种字符集。(默认使用数据库的字符集)
--master-data=2 表示在备份过程中记录主库的 binlog 和 pos 点，并在 dump 文件中注释掉这一行，在使用备份文件做新备库时会用到
-x --lock-all-tables 锁表备份。由于 MyISAM 不能提供一致性读，如果要得到一份一致性备份，只能进行全表锁定。
-l --lock-tables 锁定所有的表以便读取。(默认为打开；使用 --skip-lock-tables 来禁用。)
--dump-date 在输出的最后加上转储日期。(默认为打开；使用 --skip-dump-date 来禁用。)
-h --host=name 连接主机
-P --port=# 端口号
-u --user=username 用户名
-p --password[=passwd] 密码
-A --all-databases 转储全部数据库
-Y --all-tablespaces 转储所有的表空间。(反之 -y --no-tablespaces 不转储任何表空间信息)
-B --databases 转储指定数据库
--tables 转储指定表，覆盖选项 --databases
--ignore-table=name 不转储指定的表。要指定一个以上的忽略的表，请多次使用该指令，每个表一次为每个表使用一次。每个表都必须同时指定数据库和表名(如 --ignore-table=database.table)
-d --no-data 不转储行记录。（只有表结构，没有表数据）
--add-drop-database 在每次创建前添加一个 DROP DATABASE。
--add-drop-table 在每次创建前添加一个 DROP TABLE。(默认添加)

$ mysqldump --user=root --password=895623qq --databases=staff > staff.sql
$ mysqldump -uroot -p895623qq staff > staff.sql

3. Specified key was too long; max key length is 767 bytes

在 mysql5.6 中索引列的最大长度为 767 个字节。

varchar(255)所表示的单位是字符，而一个汉字一个字母都是一字符。所以这里可以存储 255 个汉字或者 255 个字母。utf- 8 下，1 字符 = 3 字节。（uft- 8 也称之为 utf-8mb3）utf-8mb4 下，1 字符 = 4 字节。

utf-8mb4.var_char(255)，超过了 767 个字节。

4. 字符串列 order by

进行 order by 时也可以使用表达式进行排序。

select `post_id`,`meta_value` from `wp_postmeta` where `meta_key` = "post_views_count" order by `meta_value`+0 desc;

5. 字符串转整型

常用于数字字符串和数值进行比较之前，进行处理。

a.meta_value+0

6. 整型转字符串

CONVERT(`id`,CHAR)

7.Sql 命令导入、导出

将表中的数据写入文件，请使用 SELECT … INTO OUTFILE。要将文件读回表中，请使用 LOAD DATA。

LOAD DATA local INFILE FIlePath INTO TABLE

8. 多对多关系

可以通过单个字段保存多个 id，然后使用 find_in_set，也可以建立一个中间表。按具体需求来进行选择

9.thinkphp exp

# 如果 menu 是无符号的话，函数就会报错
exp("menu", "abs(`menu`-1)")

9.decimal 类型

在 MySQL 中，DECIMAL 类型的值在查询时默认会被转换为字符串类型。如果您想保持 DECIMAL 类型的值，可以使用 CAST 函数将其转换回 DECIMAL 类型。

SELECT CAST(my_decimal AS DECIMAL(10,2)) FROM my_table;

MySQL 中的 DECIMAL 和 DOUBLE 类型都是用于存储浮点数的数据类型，但它们之间有一些区别。

DECIMAL 类型用于存储精确的小数值，它可以存储从 -10^38+ 1 到 10^38- 1 之间的数值，DECIMAL 类型的存储空间是固定的，它的存储空间是由用户指定的，可以存储小数点前后的位数。

DOUBLE 类型用于存储近似的小数值，它可以存储从 -1.7976931348623157E+308 到 1.7976931348623157E+308 之间的数值，DOUBLE 类型的存储空间是可变的，它的存储空间是由 MySQL 自动分配的，可以存储小数点前后的位数。

因此，DECIMAL 类型适用于需要精确计算的场景，如财务计算，而 DOUBLE 类型适用于需要高精度但不需要完全精确的场景，如科学计算。

10. 嵌套子查询

内层子查询不能使用外层子查询的数据，今天遇到的是在 where 条件内使用外层查询的列是，报错了。可以试着转换一下思路，内层的不做条件判断，而是把条件判断交给它的上一层。

/* 关联的文档数量 */
$sub_servers = MvServeShop::whereRaw(condition(["shop = a.shop"]))->fieldRaw('server')
            ->distinct()
            ->buildSql();

/* 报错，无法使用 a.shop */
select (select count(*）from sub_servers) as count;

/* 转变成下面这样 */
$sub_servers = MvServeShop::fieldRaw('shop,server')
            ->distinct()
            ->buildSql();

select ((select count(*) from ". $sub_servers ." As source where shop = a.shop) As servers) as count);

11.utf8mb4_unicode_ci、utf8mb4_general_ci 的区别总结

对比：

utf8mb4_general_ci: 不区分大小写，utf8mb4_general_cs 区分大小写
utf8mb4_bin: 将字符串每个字符串用二进制数据编译存储，区分大小写，而且可以存二进制的内容。
utf8mb4_unicode_ci: 校对规则仅部分支持 Unicode 校对规则算法, 一些字符还是不能支持；utf8mb4_unicode_ci 不能完全支持组合的记号。

说明

ci 是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的。
bin 是二进制, a 和 A 会别区别对待。（例如你运行:SELECT * FROM table WHERE txt = 'a'，那么在 utf8mb4_bin 中你就找不到 txt = 'A' 的那一行, 而 utf8mb4_general_ci 则可以。）
utf8mb4_general_ci 是一个遗留的校对规则，不支持扩展，它仅能够在字符之间进行逐个比较。这意味着 utf8mb4_general_ci 校对规则进行的比较速度很快，但是与使用 utf8mb4_unicode_ci 的校对规则相比，比较正确性较差。

应用上的差别

对于一种语言仅当使用 utf8mb4_unicode_ci 排序做的不好时，才执行与具体语言相关的 utf8mb4 字符集校对规则。例如，对于德语和法语，utf8mb4_unicode_ci 工作的很好，因此不再需要为这两种语言创建特殊的 utf8mb4 校对规则。
utf8mb4_general_ci 也适用德语、法语或者俄语，但会有不准。如果你的应用能够接受这些，那么应该使用 utf8mb4_general_ci，因为它速度快。否则，使用 utf8mb4_unicode_ci，因为它比较准确。

12. 忽略重复唯一键插入

插入的时候使用 ignore 关键字。

13. int(20)

int(M)的作用于 int 的范围明显是无关的，int(M)只是用来显示数据的宽度，能看到的宽度。当字段被设计为 int 类型，那么它的范围就已经被写死了，与 M 无关。

14. 随机排序

ORDER BY RAND()

15. 计算 group 分组数

select 的时候对 group 的子查询语句，进行 count 计算。

16.GROUP_CONCAT 的长度限制

配置文件修改 mysql group_concat_max_len

SET GLOBAL group_concat_max_len = 102400;
SET SESSION group_concat_max_len = 102400;

17.Group

Mysql Sql Mode：ONLY_FULL_GROUP_BY，不启用的话，sql 中 select 后面的字段必须出现在 group by 后面，或者被聚合函数包裹，不然会抛出错误，Mysql5.7 默认启用。

18. 查询活跃连接

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep';

19. 防止 SQL 注入

针对用户的输入进行过滤以及类型限制，SQL 查询时使用参数化查询，避免手动字符串拼接。

20. 查询所有执行时间大于指定时间的数据库连接，拼接 Kill 语句

SELECT
	CONCAT('KILL QUERY', id, ';') AS QUERY 
FROM
	information_schema.PROCESSLIST 
WHERE
	command = 'Execute' 
	AND time > 120;

21.mysql 查询时输出固定值的列

SELECT 'Hello, World!' AS greeting, name, age FROM users;

22. 查询指定数据库表内存大小

SELECT 
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) `Size(MB)`,
    round((data_length / 1024 / 1024), 2) `Data(MB)`,
    round((index_length / 1024 / 1024), 2) `Index(MB)`
FROM information_schema.TABLES
WHERE table_schema = 'oms'
ORDER BY (data_length + index_length) DESC;

23.Replace

当使用 REPLACE 命令替换一条已有的记录时，MySQL 会先删除原有的记录，再插入新的记录。因此，新的记录将具有一个新的主键 ID，即使它的其他属性与原有的记录完全相同。

24.In 子查询

In 子查询也可以查询一整条数据。

(customer_code, cbl_add_time) IN (SELECT customer_code, MAX( cbl_add_time) FROM customer_balance_log

25.mysqldump

mysqldump 是一个用于备份 MySQL 数据库的命令行工具。当使用 mysqldump 备份数据库时，如果备份期间有其他进程访问了相同的表，那么可能会出现锁表的情况。

备份时使用 --single-transaction 选项，它可以在不锁定表的情况下备份 InnoDB 引擎表。

26.In 子查询时不会缓存的情况

子查询使用了非确定性函数，如 NOW()、RAND()等。这些函数的返回值是不确定的，每次执行都可能返回不同的结果，因此 MySQL 无法缓存子查询的结果集。
子查询中使用了用户变量或系统变量。变量的值是可以随时改变的，因此 MySQL 无法保证缓存的结果集的正确性。
子查询中使用了 TEMPORARY 表。TEMPORARY 表是会话级别的临时表，每个会话都有自己的一份，因此 MySQL 无法在多个会话之间共享缓存。
子查询中使用了不支持缓存的存储引擎。例如，MEMORY 存储引擎不支持缓存，因此如果子查询中使用了 MEMORY 表，那么 MySQL 就无法缓存结果集。
子查询中使用了子查询本身的结果集。例如，SELECT ... FROM table1 WHERE id IN (SELECT id FROM table1 WHERE ...)，这种情况下，子查询的结果集会被用来再次执行子查询，因此 MySQL 无法缓存结果集。

即使子查询的结果集可以被缓存，MySQL 也不一定会选择缓存，具体取决于查询的复杂度、结果集的大小等因素。如果 MySQL 认为缓存结果集会带来更多的开销，那么就不会缓存结果集。

Mysql学习笔记，持续记录

找回 Mysql 的登录密码

通过 Mysql 二进制日志找回数据

批量生成修改表和字段字符集的 SQL

mysql 设置 utf8_mb4

查询某个表的所有外键

Group By

1. 使用规定

explain

1. 作用

2. 语法

3. 输出结果

4. 解释

id

select_type

table

type

possible_keys

key

key_len

ref

Extra

Analyze

1. 作用

按列去重

mysql 索引失效的场景

问题集锦

1. 空判断

2. null 和数值进行比较

3. datetime 的使用

4. FIND_IN_SET

5. 包含于匹配

6. 插入失败时，主键也会自增

7. 外键创建失败的几种原因

8. 按照指定值对结果集排序

9. 列中使用子查询

10. 多列查询

11. 允许外部 IP 登录

mysql 导出、导入数据

1. 执行 sql 文件报错

2. 导入、导出 sql 文件

3. Specified key was too long; max key length is 767 bytes

4. 字符串列 order by

5. 字符串转整型

6. 整型转字符串

7.Sql 命令导入、导出

8. 多对多关系

9.thinkphp exp

9.decimal 类型

10. 嵌套子查询

11.utf8mb4_unicode_ci、utf8mb4_general_ci 的区别总结

12. 忽略重复唯一键插入

13. int(20)

14. 随机排序

15. 计算 group 分组数

16.GROUP_CONCAT 的长度限制

17.Group

18. 查询活跃连接

19. 防止 SQL 注入

20. 查询所有执行时间大于指定时间的数据库连接，拼接 Kill 语句

21.mysql 查询时输出固定值的列

22. 查询指定数据库表内存大小

23.Replace

24.In 子查询

25.mysqldump

26.In 子查询时不会缓存的情况

WordPress3 和 jQuery（二）

CSS3新增属性(15个案例 代码 效果图 素材)

RabbitMQ 篇-深入了解 RabbitMQ 安装以及 SpringAMQP 的基础使用（声明队列和交换机、发送接收消息、配置 JSON 消息转化器）

一文彻底搞定MySQL中的JSON类型，效率飞起。

fastjson_1.2.24和Shiro(CVE-2016-4437)漏洞复现

CSS3新增属性(15个案例代码效果图素材)