mysql数据库相关知识汇总学习

Dazer007

已于 2023-07-31 12:30:37 修改

阅读量6.5k

点赞数 1

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

于 2020-05-19 20:22:42 首次发布

好记星不如烂笔头，欢迎批判转载

本文链接：https://blog.csdn.net/ab601026460/article/details/54017607

版权

DB 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

2：关于mysql字符集说明，解决不能保存表情问题

3：关于mysql密码相关

4：mysql函数相关

5： mysql 导入导出，用户名称不一样导致的问题

6： mysql服务器密码安全策略

7： mysql定时任务?

8: mysql 卡死、密码过期、连接数问题解决

You can't specify target table '表名' for update in FROM clause错误(不能在同一个sql语句中，先select同一个表的某些值，然后再update这个表)

9: mysql创建数据库并给授予最低权限

10: mysql表字符集不一致的问题

11: mysql高可用问题，主从

12： mysql 相关安装使用

13：Mysql的跨服务器关联查询--Federated引擎

14: MySql中in查询效率低的替代方法

15:数据库mysql安装

我去，原来my.ini 在 C盘的隐藏目录里面， C:\ProgramData\MySQL\MySQL Server 5.7/my.ini不在 C:\Program Files\MySQL\MySQL Server 5.7/这个目录；

16:mysql表锁

16:mysql表锁报错 ERROR 2002 (HY000)

17: SELECT INTO 和 INSERT INTO SELECT 两种表复制语句详解（SQL数据库和Oracle数据库的区别）

2：关于mysql字符集说明，解决不能保存表情问题

字符集说明：参加：字节、字、bit、byte的关系; ;;

mysql7字符集：utf8mb4、排序方式： utf8mb4_unicode_ci

The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)

utf8mb4 字符集(4字节 UTF-8 Unicode 编码)

大部分项目使用“utfm4_general_ci”就够了，如果你的项目要支持 “德语、法语或者俄语”，请使用：“utf8mb4_unicode_ci”，“utf8mb4_bin”导致区分大小写

3：关于mysql密码相关

新安装的mysql没有设置密码，错误1045

修改mysql默认密码，知道原来的密码：或者密码过期了要修改，注意新密码和之前的不能是一样的

mysql修改密码：
mysql7之前： 
-- update mysql.`user` set `password`=PASSWORD('imega') where  `user`='imega';  
-- flush privileges;

mysql7之后：mysql5.7的mysql.user表下为什么没有password字段了，改成了authentication_string字段。
#密码要够复杂，mysql也遭遇到库被删除的情况。
update mysql.`user` set authentication_string=password('root007..xX@!x9~') where `user`='imega';
flush privileges;

  select host,user,authentication_string from user;


-- 查看所有的用户 、 修改 原来的用户名称
select user,host from mysql.user;
update user set user ='ourslook' where user ='cyl';


--- mysql5.7 查看和修改密码过期 策略
show global variables like 'default_password_lifetime';  -- 一般默认180天
SET GLOBAL default_password_lifetime = 0; --- 设置是0，就是永远不过期

-- 更厉害的是全局设置一下
[mysqld]
default_password_lifetime=0

4：mysql函数相关

&-1:比如把所有用户昵称为空的人，昵称全部变成手机号中间带有四位****

UPDATE tb_xa_user SET  user_name =  concat(SUBSTR(mobile,1,3),'****',SUBSTR(mobile, -4)) WHERE length(user_name) = 0

&-2:把集合变成逗号分割的形式,经常在子查询

DELETE FROM sys_menu menu WHERE
  menu.menu_id in  (
                    (SELECT GROUP_CONCAT(a.menu_id) FROM sys_menu a LEFT JOIN sys_menu b on a.parent_id = b.menu_id WHERE a.type = 2 and b.menu_id is NULL)
                  ) ;

select t1.* , group_concat(t2.role_id) roleIdList from sys_user t1
		left join sys_user_role t2 on t2.user_id = t1.user_id
  group by t1.user_id;-- 结果：13,26,29,1,12,27,28,30

&-3: 字符串连接函数

select concat('11','22',null); 结果是<null>

select concat_ws(',','11','22','33'); 结果：11,22,33

&-4：IFNULL

SELECT o.create_time,IFNULL(o.price,0),'1' TYPE FROM tb_xa_charge_reward o

&-5:日期处理函数：

SELECT o.pay_time,ifnull(o.order_price,0) * (userRules/100),'1' type FROM tb_xa_order o where o.order_status=5 and DATE_FORMAT(o.pay_time,'%Y-%m-%d')<DATE_FORMAT(DATE_ADD(now(),INTERVAL -30 day),'%Y-%m-%d') and o.business_id=userId

DATE_FORMAT: 处理时间格式最常用：

%Y-%m-%d 四位年2位月2位天

DATE_ADD 或者 DATE_SUB :日期操作详解：

set @dt = now();

select date_add(@dt, interval 1 day); -- add 1 day 增加一天
select date_add(@dt, interval 1 hour); -- add 1 hour 增加一小时
select date_add(@dt, interval 1 minute); -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day); -- sub 1 day 减少一天；  也可以直接使用 date_sub

&-6：大小写函数、union all、ordrby rand()、存储过程

CREATE PROCEDURE `pro_home_search`(`searchKey` VARCHAR(255), `begins` INT(11), `counts` INT(11)) COMMENT '首页多模块搜索'
  SELECT id, name, type, typeName FROM (
  SELECT id AS id , title                AS name, 1  as type, '服务公共' as typeName FROM tb_xa_service_common  WHERE  status <> 3 AND (UPPER(title) LIKE searchKey OR LOWER(title) LIKE searchKey)
    UNION ALL
  SELECT id AS id , nurse_name         AS name, 12 as type, '家政护理' as typeName  FROM tb_xa_domesticnursing  WHERE  status <> 3  AND  (UPPER(nurse_name) LIKE searchKey OR LOWER(nurse_name) LIKE searchKey)
    UNION ALL
  SELECT id AS id , merchant_name        AS name, 13 as type, '医疗服务|医疗康复' as typeName  FROM tb_xa_medicalservice  WHERE  status <> 3 AND  (UPPER(merchant_name) LIKE searchKey OR LOWER(merchant_name) LIKE searchKey)
    UNION ALL
  SELECT id AS id , nurse_training_name  AS name, 14 as type, '护理培训|培训义工' as typeName  FROM tb_xa_nursetraining  WHERE  status <> 3 AND  (UPPER(nurse_training_name) LIKE searchKey OR LOWER(nurse_training_name) LIKE searchKey)
    UNION ALL
  SELECT id AS id , vsdc_name            AS name, 15 as type, '义工中心' as typeName  FROM tb_xa_vsdc  WHERE  status <> 3 AND  (UPPER(vsdc_name) LIKE searchKey OR LOWER(vsdc_name) LIKE searchKey)
    UNION ALL
  SELECT id AS id , institution_name     AS name, 2  as type, '机构' as typeName  FROM tb_xa_institution  WHERE  status <> 3 AND  (UPPER(institution_name) LIKE searchKey OR LOWER(institution_name) LIKE searchKey)
    UNION ALL
  SELECT id AS id , hotel_rooms_name     AS name, 22 as type, '机构养老院' as typeName  FROM tb_xa_inshotelroomstable  WHERE  status <> 3 AND  (UPPER(hotel_rooms_name) LIKE searchKey OR LOWER(hotel_rooms_name) LIKE searchKey)
   UNION ALL
  SELECT id AS id , shop_product_name    AS name, 3  as type, '普通商品' as typeName  FROM tb_xa_shop  WHERE  status = 2 AND  (UPPER(shop_product_name) LIKE searchKey OR LOWER(shop_product_name) LIKE searchKey)
   UNION ALL
  SELECT id AS id , works_name           AS name, 4 as type, '艺术品' as typeName  FROM tb_xa_autherworkstable  WHERE status <> 3  AND  (UPPER(works_name) LIKE searchKey OR LOWER(works_name) LIKE searchKey)
    UNION ALL
  SELECT id AS id , author_name          AS name, 42 as type,'作者' as typeName  FROM tb_xa_artinformation  WHERE  status <> 3 AND  (UPPER(author_name) LIKE searchKey  OR LOWER(author_name) LIKE searchKey)
    UNION ALL
  SELECT id AS id , travel_item_name     AS name, 5 as type, '游学|旅游' as typeName  FROM tb_xa_traveltable  WHERE  status <> 3 AND  (UPPER(travel_item_name) LIKE searchKey OR LOWER(travel_item_name) LIKE searchKey)
) RESULT
ORDER BY rand() LIMIT begins, counts

&-7：union 使用，连接多张表：

CREATE PROCEDURE pro_gains(IN userId BIGINT, IN b INT, IN c INT)
  BEGIN
	-- 变量，经销商返点规则
	DECLARE userRules int DEFAULT 0;
	-- 查询返点规则和账期给对应变量
	select s.user_rules into userRules from tb_xa_setting s where s.id=1;
	SELECT * from
		(SELECT rs.create_time,o.order_price * (userRules/100),'2' type from tb_xa_returnshoprecord rs INNER JOIN tb_xa_order o on rs.order_no=o.order_no where o.business_id=userId and rs.audit_result=1 and o.order_status=8
				UNION
		 SELECT c.create_time,c.cash_money,'3' type from tb_xa_cashrecord c where c.audit_result=1 and c.user_id=userId
				UNION
		SELECT o.pay_time,ifnull(o.order_price,0) * (userRules/100),'1' type FROM tb_xa_order o where o.order_status=5 and DATE_FORMAT(o.pay_time,'%Y-%m-%d')<DATE_FORMAT(DATE_ADD(now(),INTERVAL -30 day),'%Y-%m-%d') and o.business_id=userId
			  UNION
		SELECT o.create_time,IFNULL(o.price,0),'1' TYPE FROM tb_xa_charge_reward o WHERE o.business_id=userId and o.create_user IS NULL  and o.price IS NOT NULL and o.price<>0
			  UNION
		SELECT o.create_time,IFNULL(o.price,0),'4' TYPE FROM tb_xa_charge_reward o WHERE o.business_id=userId and o.create_user IS NOT NULL  and o.price IS NOT NULL and o.price<>0
) t ORDER BY t.create_time desc limit b,c;
end;

&-8：replace函数的坑，如果替换后的字符串是 null, 会清空该字段

UPDATE tb_token set token = REPLACE(token,'0', null); #输入的话，第二个参数如何是null的话， 本条数据就全丢失了。
UPDATE tb_token set token = REPLACE(token,'旧的字符串', IFNULL('新的字符串', '')); #输入的话，第二个参数如何是null的话， 本条数据就全丢失了。

MySQL 如何利用一条语句实现类似于if-else条件语句的判断

-- 商品价格浮动处理，不同价格区间浮动不同
update goods  
set price = (  
case   
  when price between 0 and 99 then price * 1.2  
  when price between 100 and 999 then price * 1.1  
  when price between 1000 and 1999 then price * 1.05  
  when price > 1999 then price * 1.02  
end);  
select * from goods;


-- 根据成绩显示对应的中文
select id as 学号, name as 姓名, score as 分数,   
(  
  case   
    when score >= 90 then '优秀'  
    when score >= 80 and score < 90 then '良好'  
    when score >= 60 and score < 80 then '及格'  
    when score < 60 then '不及格'  
  end  
)  
as 等级  
from scores;

&-9：日期函数

between DATE_SUB(now(),INTERVAL  3 DAY) and now()

5： mysql 导入导出，用户名称不一样导致的问题

mysql数据导出然后导入导致报错：

java.sql.SQLException: The user specified as a definer ('exiaodao'@'%') does not exist

原因是修改了数据库访问的用户，存储过程，视图，函数的definer改变了，导致访问不了

手工用navixcat修改一下definer

6： mysql服务器密码安全策略

【阿里云】尊敬的阿里云用户：

云盾检测到您云服务器106.14.66.218自建的Mysql数据库存在弱口令问题,可能会造成数据被删勒索或数据泄露的风险,请您尽快修改密码并建议您不要将Mysql管理端口开放到外网，具体修复方式参见

Mysql服务安全加固阿里云

MySQL中用户权限、库权限、表权限的控制

7： mysql定时任务?

mysql开启定时任务或者时间Event有没有开启

1:如何设置

2：怎么开启

SHOW VARIABLES LIKE 'event_scheduler'

8: mysql 卡死、密码过期、连接数问题解决

mysql表被锁定、卡死、锁死

-- 查看所有进程
show processlist;
-- 分析sql查询锁的使用情况介绍
select * from information_schema.innodb_trx;

-- 查询是否锁表
show OPEN TABLES where In_use > 0;

-- 查看被锁住的
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
-- 等待锁定
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
kill 12041;
kill   id  ;

mysql过期策略-MySQL的用户密码过期功能详解 x

expire password policy : default(默认) 、immediate(立即) 、interval（ 间隔）、never（绝不）
EXPIRE INTERVAL 30 DAY; 间隔时间30
EXPIRE NEVER; 禁用密码过期
EXPIRE DEFAULT;默认策略密码过期

mysql连接情况，Mysql 查看连接数,状态最大并发数(赞)_wsf568582678的博客-CSDN博客_mysql 查看连接数：

MYSQL 查看最大连接数和修改最大连接数

-- Mysql 查看连接数,状态 最大并发数(赞)

1、查看最大连接数
show variables like '%max_connections%';  -- 查看最大连接数
2、修改最大连接数
set GLOBAL max_connections = 1000; -- 修改mysql最大连接数



show status like 'Threads%'; -- Threads_running是代表当前并发数;Threads_connected这个数值指的是打开的连接数;
show status; -- 显示数据库的状态
show processlist; --查看具体的连接情况

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

mysql创建索引技巧，索引长度不能太长，否则报错，如：Specified key was too long; max key length is 767 bytes；

下面修改之后，如果还不起作用，请参考博客：Index column size too large. The maximum column size is 767 bytes._云丫-CSDN博客 x

-- mysql创建索引技巧，索引长度不能太长，否则报错，如：Specified key was too long; max key length is 767 bytes
-- 使用utf8mb4 一个字符使用4个字节，varchar(200) = 200 * 4 = 800 bytes,就超过了。
-- 查看索引相关的长度和和格式
show variables like 'innodb_large_prefix';   -- 正常取值ON,不正常取值OFF
show variables like 'innodb_file_format';-- 正常取值：Barracuda，不正常取值：Antelope

-- 修改最大索引长度限制
set global innodb_large_prefix=1; 
set global innodb_file_format=BARRACUDA;
-- 添加
set global innodb_file_format_max=BARRACUDA;

-------------------------------------------------------------------------------------

mysql导入导出可能出现的问题

1：索引长度过长，比如utf8mb4字符集，varchar(191.75),最大长度只能191，否则有问题

2：线上已经有数据的表，比如数据有1万条，添加表的索引、改变表的长度等alter 行为，都很慢；解决方法：表数据导出备份、清空数据、然后在修改表结构、重新导入数据；

3：导入导出，如果是有函数、存储过程等，并且两个数据库的用户名还不一样，到时也会出现问题；

比如：表开始用root用户创建，那这个函数的创建者就是root；导入另外一个用户就会出问题；

-------------------------------------------------------------------------------------

Mysql索引详解及优化(key和index区别)

-------------------------------------------------

更新的同时查询同一张表，保存：

You can't specify target table '表名' for update in FROM clause错误(不能在同一个sql语句中，先select同一个表的某些值，然后再update这个表)

You can't specify target table '表名' for update in FROM clause错误_LemonCake的博客-CSDN博客

------------------------------------------------------

mysql忘记密码CentOS7下mysql忘记root密码的处理方法xxx

参考：CentOS7下mysql忘记root密码的处理方法

9: mysql创建数据库并给授予最低权限

-- -- 删除用户和授权
-- drop user wanjieuser@'%';

-- 创建用户、设置默认数据库、修改密码过期策略;密码要包含大小写数组特殊字符等，否则不符合安全策略
CREATE USER 'hunqinguser'@'%' IDENTIFIED BY 'hunqing123X_..';
create database `hunqingdb` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 授权 SELECT VERSION();
-- mysql5.7、5.6 以及以下授权
-- 这里可以授权多个db的权限
grant all privileges on `hunqingdb`.* to 'hunqinguser'@'%' identified by 'hunqing123X_..';
-- mysql8+ 授权;   mysql8的密码机制有改动，授权完毕，需要在更改奶一下密码。
-- grant all privileges on `hunqingdb`.* to 'hunqinguser'@'%' ;
-- ALTER USER 'hunqinguser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'hunqing123X_..';


flush privileges;
 
-- 如果给ourslookPortaluser分配所有表的权限，ourslookdb 变成 *.*

查看数据库：show databases;
查看用户已经权限： SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

10: mysql表字符集不一致的问题

mysql如果字符集不一致，导致问题

1：关联表索引失效

2：不同版本mysql,有的版本mysql字符集不存在，导致表迁移失败等。

这里提供统一的修改表字符集的方法

-- 1：这里修改所有表的字符集
select CONCAT('alter table ',a.table_name,' convert to character set utf8mb4 collate utf8mb4_unicode_ci;')
from (select table_name from information_schema.`TABLES` where TABLE_SCHEMA = '您要替换的mysql数据库名称，其他的可以都不用修改') a;

-- 2：当然光修改了表的字符集，如果表字段字符集不一致也可能有问题。
-- 见博客： mysql 批量修改 表字段/表/数据库 字符集和排序规则  https://www.cnblogs.com/-renyu/p/10776020.html     可以使用
-- 这里修改所有的表字段字符集  指定数据库名称 和 不是主键 不是 int类型
SELECT *, TABLE_SCHEMA '数据库',TABLE_NAME '表',COLUMN_NAME '字段',CHARACTER_SET_NAME '原字符集',COLLATION_NAME '原排序规则',CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' MODIFY COLUMN `',COLUMN_NAME,'` ',COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') '修正SQL'
FROM information_schema.`COLUMNS` 
WHERE TABLE_SCHEMA  =  'hiberdb' and column_key != 'PRI' and character_set_name is not null;

mysql 批量修改表字段/表/数据库字符集和排序规则 <亲测客户使用>，如果是创建新的数据库参见(搜字符集) Linux centos 安装软件 xx

11: mysql高可用问题，主从

1）主从：一主一从（master/slave），从服务器异步复制。master节点记录binlog，从节点io线程去读取日志，哪会到自己的中继日志(relay log)中，slave从中继日志中读取。主库防止被恶意删除，建议从库，开启log_slave_updates选项（默认主从复制，是不记录日志的）。主从，不需要第三方插件，mysql自带。

2）高可用MHA 备高可用使用MHA（Master High Availability），这MHA需要默认三台机器才能完成。需要成本比较大，目前不用不用考虑，不做MHA。MHA是日本一个人员开发的第三方插件。另外有TMHA 只需要两台机器。

mysql主从同步, 参加 51cto内容，非常好。注意

MySQL、MySQL主从、MySQL-cluster、mgr、mha、读写分离x （碧桂园iot亲测有效）

MySQL主从复制指定不同库表同步参数说明（可以指定多个数据库、指定多个表）(目前测试无效)

1：binlog-do-db=test #可以被从服务器复制的库, 二进制需要同步的数据库名；如果有多个库，重复设置该选项,不是用逗号分割奥

## 主从复制,主库配置, 进入 /etc/my.cnf
[mysqld]
log-bin=mysql-bin-master #启用二进制日志
server-id=1     #本机数据库ID 标示
binlog-do-db=iotplatformdb      #可以被从服务器复制的库, 二进制需要同步的数据库名
binlog-do-db=iotgatewaydb #有多个库,需要被复制,重复设置该选项;
binlog-ignore-db=mysql  #不可以被从服务器复制的库


#### 配置主从复制,这里是重库
[mysqld]
server-id=2  #数据库表示不能重复。

mysql 命令行查看数据、创建表基本语句

# mysql 命令行查看数据、创建表
1:  登录mysql 
	mysql -uroot -P 33161 -p  xx密码; #或者-p之后回车，换行输入密码,eg: root/rootnewpassword..1

新密码：root007..xX@!x9~   老密码：rootnewpassword..1
2:  查看所有数据库
	show databases;
3:  切换到自己需要的数据库
	use iotplatformdb;  # uer后面是自己需要切换的db名称，比如:mysql数据库
4： 查看数据库 iotplatformdb 所有的表；
	show tables;
5:  创建数据库
	CREATE TABLE `iotplatformdb`.`tb_user4`  (
	  `id` int(0) NOT NULL AUTO_INCREMENT,
	  `user2` varchar(255) NULL,
	  `age2` varchar(255) NULL,
	  PRIMARY KEY (`id`)
	);
6:  查询指定的表。
	 select * from tb_user3;

12： mysql 相关安装使用

mysql zip版本的安装和配置

navixcate 最新的15 已经支持MongoDB了

13：Mysql的跨服务器关联查询--Federated引擎

项目中有申请的需求，就是夸服务器进行mysql表关联查询：Mysql的跨服务器关联查询--Federated引擎 - 农名工进城 - 博客园

14: MySql中in查询效率低的替代方法

[MySql中in查询效率低的替代方法](MySql中in查询效率低的替代方法_WWF_HelloWorld-CSDN博客)

15:数据库mysql安装

其他相关安装参考这个word文档：（linux下安装部署jdk7+tomcat7+mysql56+redis3 ）

centos下彻底删除MYSQL 和重新安装MYSQL

一般要修改掉3306的端口; 3306我们统一改成：33161，遇到过被黑客删库索钱的经历。

在阿里云的centos7上安装mysql5.6的方法 x

CentOS在线安装Mysql5.7 x

centos7在线yum安装mysql时官方镜像下载过慢的解决方案 x (亲测有效)

MySQL、MySQL主从、MySQL-cluster、mgr、mha、读写分离x （碧桂园iot亲测有效）

#- 在阿里云的centos7上安装mysql57.&的方法   http://blog.csdn.net/dongdong9223/article/details/52536675   
#- 这里使用mysql 官方 yum 下载巨慢。  看博客：centos7在线yum安装mysql时官方镜像下载过慢的解决方案 

#- ## 1: 下载并安装MySQL官方的 Yum Repository  https://dev.mysql.com/downloads/repo/yum/ 
#- el7、el8、el9：redhat/centos版本；-7：release的版本，顺序增加
#- 8.0   http://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm 
#- 7.x   http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm   
#- 可以查看mysql官网的文档：非常详细了：https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/  
wget -i -c  http://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm 

#- ## 2: 安装 yum Repository，大概25KB的样子，然后就可以直接yum安装了，其实就是添加了yum源
#-  cd /etc/yum.repos.d/ && ls -lh | grep mysql  --#直接通过目录查看
yum -y install mysql80-community-release-el7-7.noarch.rpm 

#- ### 2.1：查看安装了那些文件：  
yum repolist enabled | grep "mysql.*-community.*"   -- #通过安装的yum源查看


#- ##3:输入yum 命令安装 (这一步会在线下载各种依赖包，安装速度取决外网带宽) 
yum -y install mysql-community-server  
 
#- ## 4： 启动mysql-centos6： sudo service mysqld start     停止mysql: sudo service mysqld stop   查看mysql启动状态: sudo service mysqld status  
#- ## 4： 启动mysql-centos7:        停止： systemctl stop mysqld
# 测试服务器配置太低，可能导致启动失败
systemctl start mysqld 

#- ##6： 针对mysql7+的版本，查找随机的密码：
sudo grep 'temporary password' /var/log/mysqld.log  

#- ##7： 使用密码登录mysql：  mysql -uroot -p  #要修改端口，修改 /etc/my.cnf 或者 /etc/mysql/my.cnf
mysql -uroot -h localhost -P 33161 -p #直接回车输入密码；-h指定ip，-P 指定端口；-u和root中间件不要有空格

#- ##8： set global validate_password_policy=0; #降低密码安全等级,仅限 mysql7.x, mysql8.x无效  
#- ##9： ALTER USER 'root'@'localhost' IDENTIFIED BY 'root007..xX@!x9~' ; -- EXPIRE NEVER;  — 修改信息密码，密码永不过期  ；  新密码：root007..xX@!x9~   老密码：rootnewpassword..1
#- ##10: mysql查看版本，在mysql中输入：select version(); 就可以看到版本； 或者使用navixcate切换到mysql默认数据库查询即可；

mysql默认是不允许远程登录的，方法如下：
1：见博客：Mysql 5.7 开启远程连接 https://blog.csdn.net/sun614345456/article/details/53672150
使用navixcate 如果报错：1130 - Host '117.33.56.28' is not allowed to connect to this MySQL server
命令
1-1：切换到mysql数据库：use mysql;
1-1-a: SHOW VARIABLES LIKE 'validate_password%';   #查看密码等级
1-1-b: set global validate_password_policy=LOW;  #降低密码等级， 只验证长度，让root也能通过外网访问。
1-2：开启远程连接root 用户名% 所有人都可以访问password 密码；  新密码：root007..xX@!x9~   老密

1-2-a: 修改密码
update mysql.`user` set authentication_string = password('root007..xX@!x9~') where `user`='root'

修改密码，搜索修改密码；  如果默认密码忘记了，能登录服务器就能 跳过密码登录然后重置。
运行远程可以访问root
1-2-b: 授权可以远程访问root账号；
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root007..xX@!x9~' WITH GRANT OPTION;

主要一定要指定成功，执行成功截图如下：一定要有changed:1。否则就是没有成功。
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 1
1-3：FLUSH PRIVILEGES; 
1-4：退出重启mysql: service mysqld restart
2： 查看mysql 的端口号：netstat -tulpn： 查看启动应用的端口号。
3： 关闭centos防火墙
4： 查看aliyun上面是否运行3306端口（ 3306我们统一改成：33161， 遇到过被黑客删库索钱的经历。）的访问。如果直接无法访问，就是阿里云的问题；

mysql继续配置，安装了mysql要对相关字符集进行设置，如：字符集utf8mb4、连接属性编码： utf8mb4_unicode_ci；否则项目中无法支撑表情的，说明：utf8mb4_unicode_ci和utf8mb4_general_ci区别，general速度快但对德语或者语言部分区分不好，扩展性差，一般就用 uncode_ci把。

mysql要支持表情的话，必须是mysql的版本必须为v5.5.3或更高才能；
1: 登录mysql服务器：mysql -uroot -p  xx密码;
2: 查看相关字符集设置，随便那个用户下面都可以查看：
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';  
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)，如果是上面这个场景是可以支持表情的；除了一个 binary 和 utf-8别的是应该全是utf8mb4
windows找到my.ini、
linux centos7 在 /etc/my.cnf，编辑该文件，在最后面添加如下：
修改数据库目录
 
3:
[mysqld]

#新目录一定要授权，否则启动不起来的； chown -R mysql.mysql mysql  #复制完数据后，一定要将目录拥有者改为mysql.mysql
datadir=/usr/local/mysql-data #默认路径/var/lib/mysql 防止默认路径撑满磁盘;

# 修改默认端口； 默认端口3306,我们修改一下.使用默认端口和弱密码经常遭到攻击
port=33161 
# 服务端默认字符集
character-set-server=utf8mb4

# 连接层默认字符集
collation-server=utf8mb4_unicode_ci

#设置SQL模式，解决高版本group by 报错； 在5.7之后要设置，防止报错 “Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#设置mysql密码过期策略，这里设置为0，所有账户永不过期，单位是天，详见 https://blog.csdn.net/jc_benben/article/details/77934469
default_password_lifetime=0 #如果设置为零标识永不过期

#设置默认时区，为北京时间，东8区, 查看时区：show variables like "%time_zone%";
default-time_zone = '+8:00'

# 允许最大连接数，mysql默认连接数100，特别小，不够。 分布式架构，如果一个一个应用20个连接数，集群个数10个，就不够了。生产服务器，比较好的点的服务器，连接数可以上万。
max_connections=2000

# 设置任何ip都可以访问。0.0.0.0 任何ip;  一般不需要设置的，在授权给用户的时候设置; 影响  netstat -tulnp   的localaddress
bind-address=0.0.0.0

[client]
# 客户端来源数据的默认字符集
default-character-set = utf8mb4
socket=/usr/local/mysql/mysql.sock  # ERROR 2002 (HY000) 报错，核心就这一句，路径内容同：[mysqld] #socket=/var/lib/mysql/mysql.sock   ；可能是我们自己修改了 数据安装路径 会上面的报错提示


[mysql]
# 数据库默认字符集
default-character-set = utf8mb4

# mysql 索引过长1071-max key length is 767 byte ; 747/4=191 https://www.cnblogs.com/littleatp/p/4612896.html
-- show variables like 'innodb_large_prefix';   -- 正常取值ON,不正常取值OFF
-- show variables like 'innodb_file_format';-- 正常取值：Barracuda，不正常取值：Antelope
-- 修改最大索引长度限制
-- set global innodb_large_prefix=1;
-- set global innodb_file_format=BARRACUDA;
-- 添加
-- set global innodb_file_format_max=BARRACUDA;
---------------------
--default-storage-engine=INNODB
--innodb_large_prefix=on

 
4:重启服务器，才能看到效果；
window:进入cmd 输入net stop mysql57,然后输入net start mysql57
linux centos : 关闭、在重启  sudo service mysqld restart
 
-------------------------------------------------------------
当然还可以设置表级别、行级别的字符集设置，如下：
2-1:设置colum级别
 ALTER table xxxxxxxx_table_name  modify  clientName  varchar(100) character set utf8mb4 collate utf8mb4_unicode_ci;
2-2：设置表级别
 ALTER table mb_touchpay_record charset=utf8mb4; 
2-3：设置整个库的
set names utf8mb4;
修改后，不用重启，就可以看到效果；
 
x
 
 -------------------------------------------------------------
数据库类型补充说明：
character_set_client：客户端请求数据的字符集  
character_set_connection：客户机/服务器连接的字符集  
character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server:指定的字符集，这个变量建议由系统自己管理，不要人为定义,只有这个是utf8，别的全是utf8mb4。  
character_set_filesystem：把os上文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的  
character_set_results：结果集，返回给客户端的字符集  
character_set_server：数据库服务器的默认字符集  
character_set_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。  
collation_connection:
collation_database:
collation_server:
-------------------------------------------------------------
 
如果只是某个字段需要 只需要修改那个字段的字符集就可以了  
另外服务器连接数据库 Connector/J的连接参数中，不要加characterEncoding参数。 不加这个参数时，默认值就时autodetect。
 
--设置xxx：
set collation_connection=utf8mb4_unicode_ci;
set collation_database=utf8mb4_unicode_ci;
set collation_server=utf8mb4_unicode_ci;
 
-- 修改数据库字符集：  
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;  
-- 修改表的字符集：  
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  
-- 修改字段的字符集：  
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

 -------------------------------------------------------------
数据库类型补充说明：
character_set_client：客户端请求数据的字符集  
character_set_connection：客户机/服务器连接的字符集  
character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server:指定的字符集，这个变量建议由系统自己管理，不要人为定义,只有这个是utf8，别的全是utf8mb4。  
character_set_filesystem：把os上文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的  
character_set_results：结果集，返回给客户端的字符集  
character_set_server：数据库服务器的默认字符集  
character_set_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。  
collation_connection:
collation_database:
collation_server:
-------------------------------------------------------------

如果只是某个字段需要 只需要修改那个字段的字符集就可以了  
另外服务器连接数据库 Connector/J的连接参数中，不要加characterEncoding参数。 不加这个参数时，默认值就时autodetect。

--设置xxx：
set collation_connection=utf8mb4_unicode_ci;
set collation_database=utf8mb4_unicode_ci;
set collation_server=utf8mb4_unicode_ci;

-- 修改数据库字符集：  
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;  
-- 修改表的字符集：  
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  
-- 修改字段的字符集：  
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

mysql远程登录、密码、表情问题都解决了，现在解决，在mysql 5.7之后：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre 的问题，解决见：

切换到对应的自己的数据库，执行下面命令：

切换数据库：use xxxxxxmyDb;
查询：select @@global.sql_mode
设置：set @@global.sql_mode = 
'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql数据库相关知识汇总学习_dazer的专栏-CSDN博客

初次安装Mysql5.7以上版本后初始root密码找不到的问题 x

mysql5.7找随机密码、登录修改密码安全等级、重置root密

x Windows 下 MySql 5.7.20安装及data和my.ini文件的配置,如果没有my.ini,请在根目录自己创建x

我去，原来my.ini 在 C盘的隐藏目录里面， C:\ProgramData\MySQL\MySQL Server 5.7/my.ini
不在 C:\Program Files\MySQL\MySQL Server 5.7/这个目录；

mysql主从同步, 参加 51cto内容，非常好。注意

1：binlog-do-db=test #可以被从服务器复制的库, 二进制需要同步的数据库名；如果有多个库，重复设置该选项,不是用逗号分割奥。

16:mysql表锁

mysql锁有行级锁、表级锁，平时应该避免表级别锁。

update、delete、insert的时候都会产生锁，但update和delete的时候id必须要有索引，否则就是表锁。

一早光 这两个报错，就 4-5k次。。。，最后发现每次delete的时候锁表了
org.springframework.dao.CannotAcquireLockException
Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException


 和 org.springframework.dao.DeadlockLoserDataAccessException
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction\n;

16:mysql表锁报错 ERROR 2002 (HY000)

centos报错：centos7下启动MySQL报错ERROR 2002 (HY000)的解决办法

client]
port=3306
socket=/usr/local/mysql/mysql.sock  # ERROR 2002 (HY000) 核心就这一句，路径内容同：[mysqld] socket=/var/lib/mysql/mysql.sock   ；可能是我们自己修改了 数据安装路径 会上面的报错提示

17: SELECT INTO 和 INSERT INTO SELECT 两种表复制语句详解（SQL数据库和Oracle数据库的区别）

参：SELECT INTO 和 INSERT INTO SELECT 两种表复制语句详解（SQL数据库和Oracle数据库的区别）

参：MySQL : INSERT INTO SELECT

#### insert into 新表 （a, b） SELECT aa, bb from 老表
#### 新表，需要提前创建
INSERT into appt_person_idcard (id_card, archive_num) SELECT id_card, archive_num from appt_person;

18、mysql 随机获取 10条信息

SELECT chil_id FROM epi_child WHERE archives_upload_status = 1 ORDER BY RAND() limit 1000

19、mysql 跨库、联表更新

### 更新的时候，不要使用 left JOIN 和 right join 可能导致 匹配不上的全部置空了
#### 更新数据，切记谨慎操作。
update `appt-base`.appt_person t1 INNER JOIN  vfic_vaccinate_process t2 on t1.id_card = t2.person_id_card set t1.person_phone = t2.person_phone 
 WHERE t1.create_time <= '2021-05-10 00:00:00' and t1.person_phone = '' and t1.id_card = '612724198211220240';

20、mysql千万级大表在线添加索引

百万级别，可以在业务低峰期或者停机时候，直接加； 10几秒的样子

mysql千万级大表在线添加索引

21、mysql影子表实现数据切换 [表名修改]

千万级别，修改表名，瞬间完成.

[mysql]实现影子表数据表切换

### 百万大表，直接修改表机构，会卡死； 这里：使用影子切换
######################################
 比如，两百万的大表，修改 表结构，需要 10几秒；会影响 正常业务； 需要 10 ~ 20秒

######################################


### 1、 创建表一个空表，进行 表结构修改；
create table sys_oper_log_dazer like sys_oper_log ;  

### 2、 备份老表；切花新表。
rename table sys_oper_log to sys_oper_log_old, sys_oper_log_dazer to sys_oper_log;