mysql5.7常用语法总结

日志信息

查看日志位置
show variables like ‘general_log%’;
开启日志
set global general_log=‘ON’;

开启定时任务

查看是否开启
show VARIABLES LIKE ‘%event_scheduler%’;
select @@event_scheduler;
开启
set global event_scheduler =1; ## 0代表关闭

group_concat限制（默认1024字节）

查看长度mysql group_concat_max_len
show variables like ‘group_concat_max_len’
临时修改
SET GLOBAL group_concat_max_len = 102400;
永久修改，配置文件的[mysqld]下新增如下配置
group_concat_max_len = 102400

解锁

（1）方法1
查询是否锁表
show OPEN TABLES where In_use > 0;
查出死锁进程：
SHOW PROCESSLIST;
select * from information_schema.processlist where Command != ‘Sleep’ and state<>‘executing’ order by Time desc;

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;
杀掉进程
KILL 进程号;
（2）方法2
select * from flw_ev_databasechangeloglock
update flw_ev_databasechangeloglock set LOCKED=0 where ID=1

查看某个用户权限

show grants for 用户名

创建用户并赋予权限

创建用户并赋予权限
grant all privileges on . to ‘ele’@‘localhost’ identified by ‘123456’;
刷新权限
flush privileges;

导入数据库

进入命令行模式，导入本地路径下sql文件
source /路径/123.sql

导出数据库

mysqldump -uroot -p123456 数据库名 > 数据库名.sql

移除已存在数据库

drop database if exists 数据库名称;

最大连接数查询

SHOW VARIABLES LIKE ‘max_connections%’;

查看数据库版本

select @@version;

跳过验证登录

ps：在数据库配置文件（centos下为my.cnf）[mysqld]下添加下面内容
skip-grant-tables

全文索引

创建
alter table article add fulltext index idx_full_keyword(字段名);
检索
select count(*) from article where match(字段名) against(‘检索内容’)

创建普通索引

ALTER TABLE 表名 ADD INDEX 索引名称 (字段名)
CREATE INDEX 索引名称 ON 表名 (字段名)

创建唯一索引

ALTER TABLE 表名 ADD UNIQUE INDEX 索引名称 (字段名)
CREATE UNIQUE INDEX 索引名称 ON 表名 (字段名)

创建外键

ALTER TABLE 表名 ADD FOREIGN KEY (字段) REFERENCES 外键表名称(外键字段);

移除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

将id列设置为自增

ALTER TABLE 表名 MODIFY id INT auto_increment;

将id列设置为主键

ALTER TABLE 表名 ADD PRIMARY KEY(id)

查看已创建的索引：

show index from 表名;

索引的删除：

DROP INDEX 索引名 ON 表名
或ALTER TABLE 表名 DROP INDEX 索引名

explain中type效率指标

type：联接类型。是SQL性能的非常重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref> fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。 一般来说，得保证查询至少达到range级别。

检查数据表的状态（含编码格式）

show table status from 数据库名称 like ‘表名’;

修改表默认字符集、编码格式

ALTER TABLE 表名 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

修改表名

RENAME TABLE old_table_name TO new_table_name;

检查数据表所有字段的状态 （含编码格式）

show full columns from 表名;

修改表的所有字段的字符集

alter table 表名称 convert to character SET utf8mb4 COLLATE utf8mb4_unicode_ci;

修改字段属性默认值为VARCHAR(64)并且默认为null

ALTER TABLE 表名称 MODIFY 字段名 VARCHAR(64) NULL;

替换字符

UPDATE 表名 set 字段=REPLACE(字段,‘待替换的字符’,‘替换字符内容’)

查看存储过程

SHOW CREATE PROCEDURE 存储过程名称

执行过程

call 存储过程名称，例如：call saveOrUpdateCollege();

创建存储过程

DROP  PROCEDURE  IF EXISTS  `saveOrUpdateCollege`;
CREATE DEFINER=`app`@`%` PROCEDURE `saveOrUpdateCollege`(in dwhValue VARCHAR(32),in lsdwhValue VARCHAR(32),in dwmcValue VARCHAR(32),in school_codeValue VARCHAR(32),in dwlbm_Value VARCHAR(32))
begin
	set @count = (select COUNT(*) from college where dwh = dwhValue COLLATE utf8mb4_unicode_ci) ;

	if @count > 0 then 
		update college set dwh=dwhValue,lsdwh=lsdwhValue,dwmc=dwmcValue,school_code=school_codeValue,dwlbm=dwlbm_Value where dwh=dwhValue COLLATE utf8mb4_unicode_ci;
	else
		insert into unit (school_code) VALUES (school_code);
		set @id = (select @@IDENTITY);
		INSERT INTO college (`id`,`dwh`,`lsdwh`,`dwmc`,`school_code`,`dwlbm`) VALUES (@id,dwhValue,lsdwhValue,dwmcValue,school_codeValue,dwlbm_Value);
	END IF;
end;

DROP  PROCEDURE  IF EXISTS  `addOrUpdateInitializeUser`;
CREATE DEFINER=`app`@`%` PROCEDURE `addOrUpdateInitializeUser`( IN accountValue VARCHAR ( 64 ) )
BEGIN
SELECT id INTO @campusId FROM zc_department t WHERE org_code = @campusOrgCode and `STATUS`=0 limit 1;
SELECT id INTO @collegeId FROM zc_department t WHERE org_code = @collegeOrgCode and `STATUS`=0 limit 1;
SELECT id INTO @proId FROM zc_department t WHERE org_code = @proOrgCode and `STATUS`=0 limit 1;
SELECT id INTO @clazzId FROM zc_department t WHERE org_code = @clazzOrgCode and `STATUS`=0 limit 1;
IF @account is not null THEN 
	IF @userId IS NULL THEN
			SET @userId = MD5( UUID( ) );
		INSERT INTO zc_student (id,create_date,account,real_name,identity_card_no,phone,sex,user_type,birthday,birthplace,home_address,former_name,school,campus,college,profession,clazz,status)
		VALUES
			(@userId,NOW(),@account,@real_name,@identity_card_no,@phone,@sex,@user_type,@birthday,@birthplace,@home_address,@former_name,@schoolId,@campusId,@collegeId,@proId,@clazzId,'1');
			-- 同时初始化用户
			INSERT INTO t_s_user(id,create_date,account,real_name,identity_card_no,phone,sex,user_type,school,campus,college,profession,clazz,`status`,`password`,delete_flag,username) VALUES
			(MD5(UUID()),NOW(),@account,@real_name,@identity_card_no,@phone,@sex,IF(@user_type='teacher','labour',@user_type),@schoolId,@campusId,@collegeId,@proId,@clazzId,'1','602cc54f444f63c5','0',@account);
	ELSEIF @countLo IS NULL THEN-- 初始化用户
		SELECT
			@countLo;
			INSERT INTO t_s_user(id,create_date,account,real_name,identity_card_no,phone,sex,user_type,school,campus,college,profession,clazz,`status`,`password`,delete_flag,username) VALUES
			(MD5(UUID()),NOW(),@account,@real_name,@identity_card_no,@phone,@sex,IF(@user_type='teacher','labour',@user_type),@schoolId,@campusId,@collegeId,@proId,@clazzId,'1',`password`,'0',@account);
	END IF;
END IF;
END

使用like和drop命令进行伪删除大量数据

CREATE TABLE course_new LIKE course ;
Drop TABLE course;
ALTER TABLE course_new RENAME TO course;

触发器

同步两个系统日志功能实现。ps：两个表字段基本相同，id使用myql提供的函数进行重置，解决新增时无法获取id问题

DROP TRIGGER IF EXISTS zc_log_add; 
create trigger zc_log_add
BEFORE insert on zc_log 
for each row 
# 将公寓的日志同步到学工系统（仅限学生违纪模块）
begin 
	declare userId VARCHAR(64);
declare account VARCHAR(64);
	set @res0=LOCATE('信息1',NEW.`description`);
	set @res1=LOCATE('信息2',NEW.`description`);
	if @res0>0 OR @res1>0 THEN
-- 
	set NEW.id=MD5(UUID());
			SELECT t2.id as userId,t1.account into userId,account FROM t_s_user t1 LEFT join studentwork.t_s_user t2 on t1.account=t2.account and t2.status<>'-1' where t1.id=NEW.user_id and t1.status<>'-1'  LIMIT 1;
	if userId IS NULL THEN
				-- 如果查询不到则用学号临时代替
				set userId=account;
	end if;		
	INSERT INTO studentwork.zc_log(id, `create_name`, `create_by`, `create_date`, `user_id`, `action`, `description`, `item_id`, `param`, `create_time`, `status`) VALUES (MD5(NEW.id), NEW.create_name, NEW.create_by,NEW.create_date,userId,NEW.action,NEW.`description`,NEW.item_id,NEW.param,NOW(),NEW.`status`);
	end if;
end;

保留小数

select FORMAT(111,2);


ps:超过一定位数后会变成科学计数法

存储过程游标使用demo

CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
begin
    declare id int;
    declare realname varchar(100) character set utf8;
    declare sex varchar(1) character set utf8;
		declare age int;
    declare done int default 0;
    -- 声明游标，方便演示这里取student表数据（根据实际情况定义）
    declare mc cursor for select stu_id,stu_name,stu_age,stu_sex from student where stu_age>10;
     declare continue handler for not found set done = 1; 
-- 打开游标
    open mc;
    	-- 循环
		read_loop: LOOP
			-- 获取结果
			fetch mc into id,realname,age,sex;
			-- 声明结束
			IF done THEN
				LEAVE read_loop;
			END IF;
			-- 循环打印显示获取结果
			select id,realname,age,sex;
		END LOOP;
    -- 关闭游标
    close mc;
end

解决MySQL创建索引时报错：Specified key was too long； max key length is 767 bytes

1）先检查一下数据库被限制了索引的大小
SHOW variables like ‘innodb_large_prefix’; – OFF
2）如果查询的值是OFF的话,执行下面的命令
SET GLOBAL INNODB_LARGE_PREFIX = ON;
3）执行完了,还需要查看当前innodb_file_format引擎格式类型是不是BARRACUDA
SHOW variables like ‘innodb_file_format’; – Antelope
4）如果不是的话则需要修改
SET GLOBAL innodb_file_format = BARRACUDA;

创建视图

ps:简单的创建视图demo

CREATE OR REPLACE VIEW student_data_view AS
SELECT id,student_name,student_no where del_flag=0

关联表进行更新

以下是demo

UPDATE t_order o
INNER JOIN t_product p ON o.product_id=p.id
SET o.product_name=p.product_name