mysql常用小知识（持续更新）

1、删除表中完全重复数据

create table new_table as select DISTINCT * from old_table;
# 上面写法可以转换成以下写法
# create table new_table like old_table;
# insert into new_table select DISTINCT * from old_table;
drop table old_table;
alter table new_table rename to old_table;

2、触发器语法

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW
trigger_stmt
其中：
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

ps：程序体中获取新增数据使用new.column，已存在的数据使用old.column

示例：

CREATE TRIGGER demo1 AFTER INSERT ON t_student FOR EACH ROW
BEGIN
	UPDATE t_grand
SET total_student = total_student + 1
WHERE
	grand_id = NEW.grand_id;
END

在触发器中修改本表时，可如下操作，如新增数据时，修改其中某个字段为某值

CREATE TRIGGER t_user BEFORE INSERT ON users FOR EACH ROW 
BEGIN
set new.name = '张三'; 
END;

3、存储过程语法

create procedure sp_name()
begin
.........
end
调用call sp_name()；

示例：（动态行转列）

# 结束符号默认;, delimiter $$语句表示结束符号变更为$$
delimiter $$

CREATE PROCEDURE acv ()
BEGIN

SET @SQL = NULL;
# 动态拼接 max(case column1 when 'name' then column2 else 0 end) as 'name'语句
SELECT
	GROUP_CONCAT(
		DISTINCT CONCAT(
			'MAX( case cate_name when ''',
			cate_name,
			''' then number end ) as ''',
			cate_name,
			''''
		)
	) INTO @SQL
FROM
	`xfdp_content_taxonomy`
WHERE
	pcode = 0;

# 拼接需要执行的sql
SET @SQL = CONCAT(
	'Select year, ',
	@SQL,
	' From xfdp_content_taxonomy where pcode = 0 Group by year'
);
PREPARE stmt
FROM
	@SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

end $$

# 结束符号修改
delimiter ;

4、修改group_concat长度限制

# 如上动态行转列所示，在行转列的数据不多时没有问题，一旦数据变多，就会出现拼接的数据不完整
# 可用下面sql查看group_concat长度，默认是1024
show variables like 'group_concat_max_len';

# 修改全局group_concat长度
SET GLOBAL group_concat_max_len = 10240;

# 也可以只修改此session内的group_concat
SET SESSION group_concat_max_len = 10240;

5、数据不存在插入，存在更新或不做操作(必须现有唯一键)

insert ignore into user_info (last_name,first_name) values ('LeBron','James');
INSERT INTO table (a,b,c) VALUES (1,2,3)  ON DUPLICATE KEY UPDATE c=values(c); 

6、登录时跳过密码验证

在my.ini配置文件[mysqld]下添加skip-grant-tables，使其登录时跳过权限检查
#修改密码
update user set password=password('123465') where user='root';
# 5.7以下的是用password,5.7及以上的authentication_string

7、navicat导出表结构

SELECT
	t.TABLE_SCHEMA AS 库名,
	t.TABLE_NAME AS 表名,
	t.COLUMN_NAME AS 字段名,
	t.COLUMN_TYPE AS 数据类型,
	CASE t.COLUMN_KEY
WHEN 'PRI' THEN
	'主键'
WHEN 'UNI' THEN
	'唯一索引'
WHEN 'MUL' THEN
	'普通索引'
ELSE
	''
END AS 索引类型,
 CASE IFNULL(t.COLUMN_DEFAULT, 'Null')
WHEN '' THEN
	'空字符串'
WHEN 'Null' THEN
	'NULL'
ELSE
	t.COLUMN_DEFAULT
END AS 默认值,
 CASE t.IS_NULLABLE
WHEN 'YES' THEN
	'是'
ELSE
	'否'
END AS 是否允许为空,
 t.COLUMN_COMMENT AS 字段说明
FROM
	information_schema. COLUMNS t
WHERE
	t.TABLE_SCHEMA = '库名'
AND t.TABLE_NAME LIKE '表名';

执行上述语句，然后点击导出，选择自己想要的文件格式

8、查看连接及杀死连接

# 查看连接
show processlist;

# 展示最大连接数
show variables like "max_connections"; 

# 修改最大连接数
set GLOBAL max_connections=1000; 

# 关闭空闲连接的等待时间，默认是28800s
show global variables like 'wait_timeout'; 

# 修改等待时间
set global wait_timeout=300; 

# 关闭连接的等待时间，不仅空闲连接，正在使用的连接到了时间也会释放
set global interactive_timeout=500; 

9、查看被锁的事务

# 查看事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
# 上面运行结果中的trx_mysql_thread_id即为线程id
# 使用kill命令终止线程
kill thread_id;
# 查看锁
select * from information_schema.INNODB_LOCKS;
# 查看等待锁
select * from information_schema.INNODB_LOCK_WAITS;

10、查看死锁信息语句

show engine innodb status