Mysql学习笔记，持续记录

2023-02-17 13:34:28 浏览数 (1)

找回Mysql的登录密码

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

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

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

代码语言：javascript复制
$ service mysqld stop #停止数据库
# /usr/bin/mysqld_safe --skip-grant-tables & #后台守护进程

下面在CentOS系统继续输入:

代码语言：javascript复制
msql> mysql -u root -p

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

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

代码语言：javascript复制
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二进制日志找回数据

代码语言：javascript复制
./mysqlbinlog /usr/local/mysql/var/mysql-bin.000308 >> "3.txt"

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

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

代码语言：javascript复制
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')
代码语言：javascript复制
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配置文件

代码语言：javascript复制
[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，完事

查询某个表的所有外键’

代码语言：javascript复制
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. 语法

代码语言：javascript复制
explain select ...options

3.输出结果

代码语言：javascript复制
 ---- ------------- ------------------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ------- 
| 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 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
  • SUBQUERY 在SELECT或WHERE列表中包含了子查询
  • DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
  • UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  • UNION RESULT 从UNION表获取结果的SELECT
table

table指的就是当前执行的表

type

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

  • system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
  • const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
  • eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  • ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
  • range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
  • index， Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
  • all ，Full Table Scan 将遍历全表以找到匹配的行

注意

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

possible_keys

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

key

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

key_len

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

ref

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

Extra

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

  • Using filesort（九死一生），说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。 使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
  • Using index表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
  • Using where，表明使用了where过滤
  • Using join buffer，表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
  • impossible where，where子句的值总是false，不能用来获取任何元组 select tables optimized away，在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
  • distinct，优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

参考

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 表名查看：

代码语言：javascript复制
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按照某一字段去重，并显示其他字段信息。

代码语言：javascript复制
select * from logtest
where id in (select Max(id) from logtest group by msg)
order by create_time asc limit 3;

mysql索引失效的场景

  1. 联合索引不满足最左原则，创建联合索引最左边的那个索引字段必须在。
  2. 使用了select *
  3. 索引列上有计算，比如 select name from 表 where id = 1 2 ，导致id主键索引失效。
  4. 索引上使用了函数， select name from 表 where substr(id,1,2)=12 查询id为12开头的。
  5. 传参类型和数据库表的类型不一致，比如 select name from 表 where id =''1''(或者'1'),id在数据库是int字段，此时不会失效，因为mysql的int类型作为查询条件时，会自动将传参字符串转化为int类型。
  6. 如果是id 为varchar类型，此时传入 int类型的1这个时候索引就会失效，走全表扫描。
  7. like 条件中出现”%“开头的左模糊查询。
  8. 列对比，select name from 表 where id = height 。
  9. or关键字两端的字段都要加索引，有一个没有加索引其他所有的索引都会失效。
  10. in和exists不会使索引失效，而not in 主键索引能用，其他索引失效，not exists索引失效。
  11. order by 没有加where 或 limt ；order by 多个索引列；升序和降序混用；不满足最左原则。

问题集锦

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的值是可以同字符串一样进行拼接的

代码语言：javascript复制
/*按时间筛选*/
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

代码语言：javascript复制
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. 按照指定值对结果集排序

代码语言：javascript复制
select * from ta order by field(name,'seiki','iris','xut');

9. 列中使用子查询

代码语言：javascript复制
( 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 
	)

mysql导出、导入数据

1.执行sql文件报错

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

代码语言：javascript复制
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：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

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

代码语言：javascript复制
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

重新导入，一切OK；

2.导入、导出sql文件

代码语言：javascript复制
# 登录数据库
mysql -u -p 指定数据库
source sql文件.sql
# 等待执行完毕即可

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

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时也可以使用表达式进行排序。

代码语言：javascript复制
select `post_id`,`meta_value` from `wp_postmeta` where `meta_key` = "post_views_count" order by `meta_value` 0 desc;

5.字符串转整型

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

代码语言：javascript复制
a.meta_value 0

6.整型转字符串

代码语言：javascript复制
CONVERT(`id`,CHAR)

7.Sql命令导入、导出

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

代码语言：javascript复制
LOAD DATA local INFILE FIlePath INTO TABLE
编程算法 数据库 云数据库SQLServer sql centos

0 人点赞