From 5e5c474b46046c389b269244fe270461290e2e86 Mon Sep 17 00:00:00 2001 From: HuangCaiyun Date: Tue, 17 Dec 2019 13:59:23 +0800 Subject: add MySQL使用过程问题小结-2.md MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- ...7\256\351\242\230\345\260\217\347\273\223-2.md" | 315 +++++++++++++++++++++ README.md | 3 +- images/20190918102625947_4911.png | Bin 0 -> 439251 bytes 3 files changed, 317 insertions(+), 1 deletion(-) create mode 100644 "MySQL\344\275\277\347\224\250\350\277\207\347\250\213\351\227\256\351\242\230\345\260\217\347\273\223-2.md" create mode 100644 images/20190918102625947_4911.png diff --git "a/MySQL\344\275\277\347\224\250\350\277\207\347\250\213\351\227\256\351\242\230\345\260\217\347\273\223-2.md" "b/MySQL\344\275\277\347\224\250\350\277\207\347\250\213\351\227\256\351\242\230\345\260\217\347\273\223-2.md" new file mode 100644 index 0000000..cb0fc84 --- /dev/null +++ "b/MySQL\344\275\277\347\224\250\350\277\207\347\250\213\351\227\256\351\242\230\345\260\217\347\273\223-2.md" @@ -0,0 +1,315 @@ +## MySQL 使用过程问题小结 +### 查询命令样例 +#### [insert into 插入若引起主键冲突则更新数据](https://blog.csdn.net/t894690230/article/details/77996355) +```sql +-- 1. 不存在则插入，存在则更新 +-- 如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则执行UPDATE语句，例： +INSERT INTO `student`(`name`, `age`) VALUES('Jack', 19) + ON DUPLICATE KEY + UPDATE `age`=19; -- If will happen conflict, the update statement is executed +-- 2 row(s) affected +-- 这里受影响的行数是2，因为数据库中存在name='Jack'的数据，如果不存在此条数据，则受影响的行数为1 + +-- 如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则先删除旧数据再插入最新的数据，例： +REPLACE INTO `student`(`name`, `age`) VALUES('Jack', 18); +-- 2 row(s) affected +-- 这里受影响的行数是2，因为数据库中存在name='Jack'的数据，并且id的值会变成2，因为它是先删除旧数据，然后再插入数据，最新的表数据如下： + +-- 2. 避免重复插入 +-- 关键字/句：insert ignore into，如果插入的数据会导致UNIQUE索引或PRIMARY KEY发生冲突/重复，则忽略此次操作/不插入数据，例： +INSERT IGNORE INTO `student`(`name`, `age`) VALUES('Jack', 18); +-- 0 row(s) affected +-- 这里已经存在name='Jack'的数据，所以会忽略掉新插入的数据，受影响行数为0，表数据不变。 +``` +#### 插入测试数据带中文字符需要设置编码格式 +```sql +-- 以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法： +INSERT INTO table_name ( field1, field2,...fieldN ) + VALUES + ( value1, value2,...valueN ); +INSERT INTO dns_serverview VALUES (NULL, ' google-public-dns-a.google.com','8.8.8.8',NULL,'美国','Versign','CentOS7','BIND9','OPRDNS',500,3000,now()); +-- Query OK, 1 row affected (0.02 sec) +--【这里有个问题，插入中文字符的时候，需要设置编码格式，MySQL默认】 +``` +#### MySQL 判断字段是否为 NULL +```sql +-- 查询 MySQL 数据库表中字段为 NULL 的记录: +SELECT * 表名 WHERE 字段名 IS NULL + +-- 查询 MySQL 数据库表中字段不为 NULL 的记录: +SELECT * 表名 WHERE 字段名 IS NOT NULL +``` +#### 视图转换成表 +```sql +CREATE TABLE 表名 AS SELECT * FROM 视图名 +SELECT * INTO 表名 FROM 视图名 +``` +#### [删除重复数据](https://www.runoob.com/mysql/mysql-handling-duplicates.html) +简单思路：先把不重复的数据查询出来，根据查询出来的数据建个备份表（同原表一模一样，只是表名不同），把这些不重复的数据插入进去，然后把原来表清空，再把这个表的数据插入到原来的表里。__【这样操作虽然麻烦，但是没有查询、更新、性能上的问题】__ +```sql +CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex); +DROP TABLE person_tbl; +ALTER TABLE tmp RENAME TO person_tbl; + +ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name); +``` +#### [分组排序topn：MySQL获取分组后的TOP 1和TOP N记录](https://www.cnblogs.com/prayer21/p/6029694.html) +这里说了4种方法：1好使但sql麻烦；2没试对不好使；3试了不好使；4语句麻烦；2自身左连接在表大的情况下，太慢了；3相关子查询，还是采用同样的每个数对比的方式，慢。 + +#### 求两个表交、并、差各种集合操作sql命令 +![求两个表交、并、差各种集合操作sql命令](images/20190918102625947_4911.png) +[求两个表（查询结果）的差集](https://blog.csdn.net/yongh701/article/details/51689976) +```sql +select id FROM usertable LEFT JOIN +(select id as i from blog) as t1 +ON usertable.id=t1.i where t1.i IS NULL +``` +[求两表各种差、并集查询sql](https://blog.csdn.net/lb876864380/article/details/79560914) + +#### 数据库字典表相关(重构需要) +[mysql查询结果替换](https://blog.csdn.net/litongzero/article/details/83579026) +```sql +SELECT id, (SELECT `index_name_cn` FROM `sys_dic` WHERE dic_code='sex ' AND code_index = t.`sex` LIMIT 1) AS sex FROM user t +``` +#### 查询数据更新另一张表 +[mysql查询出数据更新到另一张表](https://blog.csdn.net/qq_41070393/article/details/84067735) +```sql +UPDATE `table_a` r +INNER JOIN (SELECT id,sn,filed1 FROM `table_b`) t ON r.b_id= t.id +SET r.sn = t.sn,r.filed1 = t.filed1 +``` +#### 查看数据库版本 +```sql +-- 方法一 +[root@master ~]# mysql --version +mysql Ver 14.14 Distrib 5.6.17, for Linux (x86_64) using EditLine wrapper +-- 方法二 +mysql> select version(); ++-----------+ +| version() | ++-----------+ +| 5.6.17 | ++-----------+ +1 row in set (0.01 sec) +-- 方法三 +mysql> status +``` +[查看mysql版本&端口号](https://blog.csdn.net/nemo2011/article/details/8990871/)：`show global variables like 'port';` +#### [根据查询结果建表](https://blog.csdn.net/u013803262/article/details/44783111) +```sql +create table 表名 ( + select sno , sname ,dept from s where s.sno in (select sno from sc where sc.grade >= 80) +); +``` +### 出错解决方案 +#### [解决mysql"Access denied for user'root'@'IP地址'"问题](https://www.cnblogs.com/charlesblc/p/5904522.html) +```sql +-- 查看 MySQL 数据库主机用户密码权限情况 +select host, user, Password, grant_priv from mysql.user; +-- +-----------+--------+-------------------------------------------+------------+ +-- | host | user | Password | grant_priv | +-- +-----------+--------+-------------------------------------------+------------+ +-- | localhost | root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | Y | +-- | master | root | | Y | +-- | 127.0.0.1 | root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | Y | +-- | ::1 | root | | Y | +-- | localhost | | | N | +-- | master | | | N | +-- | % | lizhao | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N | +-- | localhost | hive | *4DF1D66463C18D44E3B001A8FB1BBFBEA13E27FC | N | +-- | % | hive | *4DF1D66463C18D44E3B001A8FB1BBFBEA13E27FC | N | +-- | % | root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | Y | +-- +-----------+--------+-------------------------------------------+------------+ +-- 10 rows in set (0.00 sec) +-- pymysql.err.OperationalError: (1045, "Access denied for user 'root'@'master' (using password: YES)") + +-- 1. 首先用 python 脚本连接数据库的操作，被认为是 host 为 master 的操作 +-- 2. 其次 host 为 master 和 user 为 root 对应是没有密码的，但用 python 连接时使用了密码 + +-- 果然，在 python 中注释掉密码后，连接数据库就 OK 了。 +``` +#### 数据库链接提前关闭导致后续插入出错 +```python +# 运行 python 脚本遇到这个错误： +# raise err.InterfaceError("(0, '')") +# pymysql.err.InterfaceError: (0, '') + +# 2019-06-17 20:54:58，出现这个问题看一看是不是数据库链接提前关闭了，导致后续插入接口问题 +``` +#### [锁超时：MySQL 各种超时参数的含义](https://www.cnblogs.com/xiaoboluo768/p/6222862.html) +innodb_lock_wait_timeout 默认安装时值为50s，超过这个时间会报 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 错误。可以通过 `set innodb_lock_wait_timeout=XXX;` 修改参数值。对于这个参数，session和global级别并没有什么特别，session级别只对当前连接生效，global级别只对新的连接生效 + +【这个最后没有搞定，__如果50s还没有查出来的结果，600s也查不出来__，还是 __写的 sql 语句存在问题__，仔细检查sql语句，一种是语句中同时对一个表进行了查询和更新操作，相当于同时读写，虽然sql语法没有报错，但是会一直锁死，更新不了；一种是直接sql语法提示有问题，无法插入更新】 + +#### 查看数据库版本是否支持 partition 查找 +```sql + SELECT + DISTINCT srv.srv_name AS srvname, ip.ip_longitude AS iplongitude, ip.ip_latitude AS iplatitude, srv.srv_ipv4 AS srvIpv4, srv.srv_ipv6 AS srvIpv6, srv.srv_ip_country AS srvipcountry, srv.srv_org_name AS srvorgname, srv.srv_os AS srvos, srv.srv_soft AS srvsoft, srv.srv_role AS srvrole, srv.srv_user_count AS srvusercount, srv.srv_fxcs AS srvfxcs, srv.stat_time AS stattime + FROM + (SELECT *,ROW_NUMBER() over (PARTITION BY srv_role ORDER BY srv_user_count DESC) rn FROM dns_server_view + WHERE srv_role='RDNS' + ) srv + JOIN dns_ip_view ip ON srv.srv_ipv4 = ip.ip OR srv.srv_ipv6 = ip.ip + WHERE srv.rn <=30 + ORDER BY srv.srv_role, srv.rn + +-- 1 queries executed, 0 success, 1 errors, 0 warnings + +-- 查询：SELECT DISTINCT srv.srv_name AS srvname, ip.ip_longitude AS iplongitude, ip.ip_latitude AS iplatitude, srv.srv_ipv4 AS srvIpv4, ... + +-- 错误代码： 1064 +-- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY srv_role ORDER BY srv_user_count DESC) rn FROM dns_server_view +-- ' at line 4 + +-- 执行耗时 : 0 sec +-- 传送时间 : 0 sec +-- 总耗时 : 0.001 sec + +``` + +1. [MySQL5.5不支持partition查找](https://blog.csdn.net/bit_sky/article/details/50144389) +2. [MySQL5.6支持分区partition，但采用 plugin 方式](https://blog.csdn.net/dongdong9223/article/details/72291698) +```sql +show variables like '%partition%'; +Empty set (0.00 sec) +-- 如果没有看到变量：have_partition_engine 的值为YES，而是为empty，则说明当前mysql的版本就不支持分区。mysql从5.1开始支持分区功能。 +-- 但是如果你使用mysql的5.6版本，同样会出现empty的结果。这又是怎么回事呢，难道mysql的5.6版本不支持分区了吗？不是的，5.6依然支持分区，只不过将上面的验证方式抛弃了，改用： +show plugins; +-- 检查里面 partition 状态 +-- +----------------------------+----------+--------------------+---------+---------+ +-- | Name | Status | Type | Library | License | +-- +----------------------------+----------+--------------------+---------+---------+ +-- | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | +-- | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | +-- | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | +-- | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | +-- | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | +-- | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | +-- | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | +-- | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | +-- | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | +-- | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | +-- | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | +-- | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | +-- | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | +-- | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | +-- | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +-- +----------------------------+----------+--------------------+---------+---------+ +-- 42 rows in set (0.02 sec) +``` + +#### 数据库密码重置 +[Linux下MySQL忘记root密码的完美解决方法](https://www.linuxidc.com/Linux/2018-05/152586.htm) +```sql +-- 一、更改my.cnf配置文件 +-- 0、MySQL 版本查看 +mysql --version +-- 1、用命令编辑/etc/my.cnf配置文件，即：vim /etc/my.cnf 或者 vi /etc/my.cnf 或者 nano /etc/my.cnf +-- 2.在[mysqld]下添加skip-grant-tables，然后保存并退出 +-- 3.重启mysql服务：service mysqld restart + +-- 二、更改root用户名 +-- 1、重启以后，执行mysql命令进入mysql命令行 +-- 2、修改root用户密码 +MySQL> UPDATE mysql.user SET Password=PASSWORD('新密码') where USER='root'; +MySQL> flush privileges; +MySQL> exit +-- 3、最后把/etc/my.cnf中的skip-grant-tables注释掉，然后重启mysql，即：service mysqld restart +-- OK，下面我们就可以使用root新的密码登录MySQL了。 + +mysql -u root -p +``` +1. [重启服务出现问题：Redirecting to /bin/systemctl restart mysql.service](https://blog.csdn.net/crazyboy12138/article/details/80106481)：先 `ps -ef|grep mysql` 找到 mysqld 的线程号 ，再 `kill -s 9 xxx` 杀了后，再 `systemctl start mysqld.service &` ，之前没加 & 半天没反应，后来再看 ps 才发现启动好了。 +```bash +# 使用如下命令重启、启动、停止 mysql 服务： +systemctl restart mysqld.service +systemctl start mysqld.service +systemctl stop mysqld.service +``` + +2. [修改密码出现问题：Mysql5.7.18.1修改用户密码报错ERROR 1054 (42S22): Unknown Column 'Password' In 'Field List'解决办法](https://www.cnblogs.com/wangbaobao/p/7087032.html)：错误的原因是 5.7版本下的mysql数据库下已经没有password这个字段了，password字段改成了authentication_string +```sql +mysql -u root -p +Enter password: ******** +Welcome to the MySQL monitor. Commands end with ; or \g. +Your MySQL connection id is 12 +Server version: 5.7.18-log MySQL Community Server (GPL) + +Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. + +Oracle is a registered trademark of Oracle Corporation and/or its +affiliates. Other names may be trademarks of their respective +owners. + +Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. + +mysql> use mysql; +Database changed +mysql> select User from user; #此处为查询用户命令 ++-----------+ +| User | ++-----------+ +| ******* | +| mysql.sys | +| root | ++-----------+ +3 rows in set (0.00 sec) + +mysql> update user set password=password("*******") where user="*******"; #【修改密码报错】 +ERROR 1054 (42S22): Unknown column 'password' in 'field list' +mysql> update mysql.user set authentication_string=password('*******') where user='*******'; #修改密码成功 +Query OK, 1 row affected, 1 warning (0.00 sec) +Rows matched: 1 Changed: 1 Warnings: 1 + +mysql> flush privileges; #立即生效 +Query OK, 0 rows affected (0.00 sec) + +mysql> quit +Bye + +n>mysql -u ******* -p #以该用户登录成功. +Enter password: ******** +………………………… +mysql> +``` + +#### 数据库拒绝sqlyog远程连接问题 +[Host is not allowed to connect to this MySQL server解决方法](https://blog.csdn.net/bingjianit/article/details/54384299)：其实是MySQL不允许远程登录，所以远程登录失败了，解决方法如下： + +1. 在装有MySQL的机器上登录MySQL mysql -u root -p密码 +2. 执行use mysql; +3. 执行update user set host = '%' where user = 'root';这一句执行完可能会报错，不用管它。 +4. 执行FLUSH PRIVILEGES; + +经过上面4步，可以解决。注: 第四步是刷新MySQL的权限相关表，一定不要忘了，我第一次的时候没有执行第四步，结果一直不成功，最后才找到这个原因。 + +### 参考资料 +1. [一千行 MySQL 学习笔记](https://shockerli.net/post/1000-line-mysql-note/)：总结得很不错，推荐。 +2. [关于MySQL的lock wait timeout exceeded解决方案](https://segmentfault.com/a/1190000015314171)：这里说了几种方法可以排查出现锁超时的原因，mark了，没试。 diff --git a/README.md b/README.md index d0f5446..bb1012f 100644 --- a/README.md +++ b/README.md @@ -6,6 +6,7 @@ 1. 2019-09-15 17:00:48 ，添加 [Storm开发过程问题小结-1.md](./Storm开发过程问题小结-1.md) 2. 2019-10-15 12:29:23 ，添加 [Storm开发过程问题小结-2.md](./Storm开发过程问题小结-2.md) 3. 2019-11-17 18:36:50 ，添加 [MySQL使用过程问题小结-1.md](./MySQL使用过程问题小结-1.md) +4. 2019-12-17 13:58:13 ，添加 [MySQL使用过程问题小结-2.md](./MySQL使用过程问题小结-2.md) ## TODO -下次预告：MySQL使用过程问题小结-2，SQL语句及同步相关 +下次预告：MySQL使用过程问题小结-3，SQL语句及同步相关 diff --git a/images/20190918102625947_4911.png b/images/20190918102625947_4911.png new file mode 100644 index 0000000..a170a21 Binary files /dev/null and b/images/20190918102625947_4911.png differ -- cgit v1.2.3