现在位置: 首页 / Mysql / Mysql / 正文

Mysql学习笔记，持续记录

  • 友人a丶
  • Mysql
  • 2016-12-21
  • 1272热度
  • 0评论

找回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优化了查询，并发现所有查询所需的数据都可以从现有索引中获取，因此不需要访问表本身。
参考
https://blog.csdn.net/why15732625998/article/details/80388236

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索引失效的场景

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

  1. 外键的字段与关联的字段的类型不匹配（包括编码格式的不匹配）
  2. 外键的名字是一个已经存在的键值，要保证名字的唯一
  3. mysql引擎引起的外键创建不能保存或者不能发挥作用的情况，mysql一般的默认引擎是myisam，而myisam是不能创建外键的。
  4. 试图创建的一个外键没有建立起索引，或者不是一个primary key 并且如果其中不是一个primary key,你必须为他创建一个索引。
  5. 外键的动作设置成on delete set null 或者 on update set null，但是在关联的表的字段又设置的no null，导致冲突。
  6. 在navicat设定的表格主键中 有 主键1，主键2，主键3，的区别，主键1不能被级联更新（删除）（CASCADE）
  7. 外键存在默认值，引起的冲突
  8. 混合键值缺少索引引起的情况，需要为他单独申请索引
  9. 在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)
  1. ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的，因为列不在GROUP BY从句中
  2. STRICT_TRANS_TABLES：严格模式，在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制
  3. NO_ZERO_IN_DATE：在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入’0000-00-00’。在非严格模式，可以接受该日期，但会生成警告。（注：但可以插入“0000-00-00和0000-01-01”）
  4. NO_ZERO_DATE：在严格模式，不要将 '0000-00-00’做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告（注：年月日中任意一个不为零都可插入，全为0报错）
  5. ERROR_FOR_DIVISION_BY_ZERO：在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL。
  6. NO_AUTO_CREATE_USER：防止GRANT自动创建新用户，除非还指定了密码。
  7. 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引擎表。

扩展阅读
https://zhuanlan.zhihu.com/p/55634944

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认为缓存结果集会带来更多的开销，那么就不会缓存结果集。