MySQL常见问题

MySQL常见问题

你有没有遇到过，在开开心心的设计完库表之后，打开db客户端准备执行ddl语句时候愣住了，不得不打开浏览器搜索下某段很简单但是总忘的SQL语句。有没有遇到过某个MySQL问题出现一次之后，当下解决了问题，下一次又出现时由于没有总结记录，不得不再次打开搜索如何解决。这篇文章是我日常工作当中的积累，希望对你有帮助。😁

1、新增一列并设置字段类型varchar(8)

alter table tb add column remark varchar(8);

2、给某个字段默认值操作

-- 删除默认值
alter table tb alter column remark drop default;

-- 添加默认值
alter table tb alter column remark
                default 'this is default value';

3、查询某库（db_01）的表(tb_01)结构

说明：如果where条件中不带上table_name,默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了。

select
	COLUMN_NAME as '字段名称',
	COLUMN_TYPE as '数据类型',
	if(IS_NULLABLE = 'NO', '是', '否') as '必填',
	COLUMN_COMMENT as '注释'
from
	INFORMATION_SCHEMA.COLUMNS
where
	table_schema = 'db_01'
	and
	table_name = 'tb_01'

4、GRUOP_CONCAT函数

说明：根据code分组按照创建时间排序，id组成逗号相隔的字符串

select GROUP_CONCAT(id order by create_date desc)
  from tb group by code

5、自定义自增序号列rank

select (@i:= @i+1) as rank, id,name,code
  from tb a
  join (SELECT @i:=0) as i

6、触发行锁的条件

MySQL中innodb存储引擎支持行锁，innodb的行锁是通过索引加锁实现的，意味着只有通过索引条件检索数据的额时候才能使用行级锁，否则表锁。

7、将字符串类型转化成int类型

select CONVERT('23',SIGNED)

8、本地安装MySQL出现问题

当启动数据库net start mysql无效时，进入MySQL安装目录，执行mysqld --install，加载完毕后启动服务。

9、设置MySQL密码

-- 修改当前用户密码
set password = password('123456');

-- 设置初始密码
update MySQL.user set authentication_string=password('123456') where user='root' ;

10、解决出现的sql_mode错误

错误报文：this is incompatible with sql_mode=only_full_group_by

SET GLOBAL sql_mode = ''
SELECT @@GLOBAL.sql_mode

11、存储过程

-- 循环插入
create procedure myproc()
begin
  declare num int;
  set num=1;
  while num < 10 do
  insert into set num=num+1;
end while;
end

-- 调用存储过程
call myproc

12、本周时间

-- 本周周一的日期
select subdate(curdate(), date_format(curdate(),'%w')-1);

-- 本周周日的日期
select subdate(curdate(), date_format(curdate(),'%w')-7);

13、出现乱码，更改字符串的编码

CONVERT(name USING gbk)

14、FIELD函数

-- 查询结果集进行指定顺序排序，
-- asc则将未匹配到的数据放在最后，
-- desc将匹配到的数据放在最前面
select * from tb order by field(id, 20,28) desc

15、两个日期期间的天数

select datediff(now(), '2022-01-01')

16、String类型转日期DATE_FORMAT函数

select DATE_FORMAT('2002-12-12', '%Y-%m-%d')

17、异常，数据库Too many connections

说明：information_schema.processlist 每个连接进程的详情

17.1、查看连接数，处于sleep状态的用户连接直接kill掉

show processlist;

17.2、查看最大连接数，超过这个连接数则出现该错误

show variables like 'max_connections';

17.3、修改最大连接数

set global max_connections=1000;

17.4、查看连接睡眠时间，等待时间过长会占用连接数。默认8h

show global variables like 'wait_timeout';

17.5、重新设置连接睡眠时间

set global wait_timeout=600;

17.6、小技巧，批量生成需要kill的进程的命令

select concat('KILL ',id,';') from information_schema.processlist where user='root';

17.7、执行kill命令

Kill 1002;

18、表空间的相关操作

18.1、临时表空间

18.1.1创建临时表空间

create temporary tablespace tbspc_temp;

18.1.2临时表空间实例设置大小，逐步增加的限制，以及最大空间

tempfile 'D:\design\tbspc_temp_01_20220730.dbf' size 100m autoextend on next 50m maxsize 200m;

18.1.3删除临时表空间

drop tablespace tbspc_temp including contents and datafiles;

18.2、表空间

18.2.1创建表空间

create tablespace tbspc;

18.2.2表空间实例设置大小，逐步增加的限制，以及最大空间

datafile 'D:\design\tbspc_01_20220730.dbf' size 200m autoextend on next 100m maxsize 400m;

18.2.3删除表空间

drop tablespace tbspc
including contents and datafiles;

18.2.4修改表空间设置策略

alter tablespace tbspc
add datafile 'D:\design\tbspc_01_20220730.dbf' size 200m autoextend on;

18.3、创建用户默认表空间以及临时表空间

create user aaaaa_user identified by aaaaa_pwd
default tablespace tbspc
temporary tablespace tbspc_temp;

18.4、账号赋权

grant dba to aaaaa_user;