MySQL常用命令

查看、启动、重启、关闭

# 查看mysql
> systemctl status mysqld.service;

# 启动mysql
> systemctl start mysqld.service;

# 重启mysql
> systemctl restart mysqld.service;

# 关闭mysql
> systemctl stop mysqld.service;

导入、导出SQL文件

# 导出
> mysqldump -uroot -p database > /home/database.sql
> mysqldump -uroot -p database table > /home/table.sql

# 导入
> mysql -u root -p
mysql> use database;
mysql> source /home/database.sql

高效导入、导出数据

-- 导出
select '字段' from table -- 可以加where条件
into outfile '/home/file.txt' -- 导出文件
fields terminated by ',' -- 字段分割符 字段之间用 逗号 分割
lines terminated by '\n'; -- 换行符

-- 导入
load data
infile '/home/file.txt' -- 加载文件
ignore into table xxx -- 插入记录 ignore重复写入 replace替换写入
fields terminated by ','  -- 字段分割符 字段之间用 逗号 分割
lines terminated by '\n'; -- 换行符

数据迁移

# 单个或多个数据库
mysqldump -h远程ip -u用户 -p密码 -P3306 --default-character-set=utf8  --databases 单个或多个数据库名称空格分开 | mysql -h127.0.0.1 -uroot -p -P3306

# 所有数据库
mysqldump -h远程ip -u用户 -p密码 -P3306 --default-character-set=utf8 --all-databases | mysql -h127.0.0.1 -uroot -p -P3306

# 某库某表
mysqldump -h远程ip -u用户 -p密码 -P3306 --default-character-set=utf8  dbname table1 table2…… | mysql -h127.0.0.1 -uroot -p -P3306

常用函数

-- 修改数据库字符集及排序规则
alter database table character set utf8 collate utf8_general_ci;

-- 获取当前时间
select now();

-- 获取当前时间戳
select unix_timestamp() * 1000;

-- 时间转时间戳
select unix_timestamp('日期') * 1000;

-- 时间戳转时间
select from_unixtime(time / 1000);

-- 时间戳格式化
select from_unixtime(time / 1000, '%Y-%m-%d');
select from_unixtime(time / 1000, '%Y-%m-%d %H:%i:%s');

-- 时间格式化
select date_format(now(), '%Y-%m-%d');
select date_format(now(), '%Y-%m-%d %H:%i:%s');

-- 完整时间格式符列表
%a  缩写星期名 (Sun..Sat)
%b  缩写月名 (Jan..Dec)
%c  月，数值 (0..12)
%D  带有英文前缀的月中的天 (1st, 2nd, 3rd, 等等)
%d  月的天，数值 (00..31)
%e  月的天，数值 (0..31)
%f  微秒 (000000..999999)
%H  小时 (00..23)
%h  小时 (01..12)
%I  小时 (01..12)
%i  分钟，数值 (00..59)
%j  年的天 (001..366)
%k  小时 (0..23)
%l  小时 (1..12)
%M  月名 (January..December)
%m  月，数值 (00..12)
%p  AM 或 PM
%r  时间，12-小时 (hh:mm:ss AM 或 PM)
%S  秒 (00..59)
%s  秒 (00..59)
%T  时间，24-小时 (hh:mm:ss)
%U  周 (00..53) 星期日是一周的第一天
%u  周 (00..53) 星期一是一周的第一天
%V  周 (01..53) 星期日是一周的第一天，与 %X 使用
%v  周 (01..53) 星期一是一周的第一天，与 %x 使用
%W  星期名 (Sunday..Saturday)
%w  周的天 (0=星期日..6=星期六)
%X  年，其中的星期日是周的第一天，4 位，与 %V 使用
%x  年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y  年，4 位
%y  年，2 位

-- 日期添加指定的时间间隔
-- 可选择类型 年(year), 季(quarter), 月(month), 周(week), 天(day), 小时(hour), 分钟(minute), 秒(second), 毫秒(microsecond)，默认天
select adddate('日期', interval num day);

-- 日期减去指定的时间间隔
-- 可选择类型 年(year), 季(quarter), 月(month), 周(week), 天(day), 小时(hour), 分钟(minute), 秒(second), 毫秒(microsecond)，默认天
select subdate('日期', interval num day);

-- 两个日期之间的时间差
-- 可选择类型 年(year), 季(quarter), 月(month), 周(week), 天(day), 小时(hour), 分钟(minute), 秒(second), 毫秒(microsecond)
select datediff('开始日期', '结束日期');
select timestampdiff(interval, '开始日期', '结束日期');

-- 字符串拼接
select concat('字段', '字段', '...');

-- 数据格式 位数补全 左填充
select lpad('#', 16, 0);

-- 数据格式 位数补全 右填充
select rpad('#', 16, 0);

-- 模糊查询(like效率最低，条件左右亦可)
select '字段' from '表名' where '字段' like '%_%';
select '字段' from '表名' where instr('字段', '');
select '字段' from '表名' where locate('', '字段');
select '字段' from '表名' where position('' in '字段');
select '字段' from '表名' where find_in_set('', '字段');

-- join用法
left join : 两个表的交集外加左表剩下的数据;
right join : 两个表的交集外加右表剩下的数据;
inner join : 两个表的交集;
cross join : 将A表的每一条记录与B表的每一条记录强行拼在一起;
using : using('字段')的功能相当于on '字段' = '字段'，using会去除指定的列，而on不会;

-- 分组后字段合并
select group_concat([distinct] '字段' [order by] [separator '分隔符']) from '表名' group by '字段';

-- 按指定字段内容排序
select * from '表名' order by field('字段', '值1', '值2', '值3',...);

-- 生成连续日期
create table date (i int(1));
insert into date (i) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
select
adddate('开始日期', dates.i) date
from (
  select
  d1.i + d10.i * 10 + d100.i * 100 i 
  from date d1
  cross join date d10
  cross join date d100 
) dates 
where '结束日期' >= adddate('开始日期', dates.i);

-- 累加
select 
(@i := @i + num) 
from t 
cross join (select @i := 0) x;

-- 表添加字段
alter table 表名 add column 字段名 varchar(255) character set utf8 collate utf8_general_ci null default '' comment '描述' after 字段名;

正则替换(mysql8.0)

select regexp_replace(source, pattern, replace)

预定义的 POSIX 字符类

字符类	说明
[:alpha:]	字母字符
[:lower:]	小写字母字符
[:upper:]	大写字母字符
[:digit:]	数字
[:alnum:]	字母数字字符
[:space:]	空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符
[:punct:]	标点字符
[:cntrl:]	控制字符（禁止打印）
[:print:]	可打印字符

使用 CTE 实现递归(mysql8.0)

-- recursive 必不可少，cte 方法命名
with recursive cte as (
  -- 初始查询条件
  select * from org where id = ''
  union all
  -- 要递归的数据
  select o.* from org o, cte where o.id = cte.pid
) 
select * from cte

排名统计(mysql8.0)

-- rank
-- 跳跃、间断排名，例：1、1、3、4
select score, rank() over(order by score desc) from score;
-- 分组排序
select score, rank() over(partition by xxx order by score desc) from score;

-- row_number
-- 连续、不重复排名，例：1、2、3、4
select score, row_number() over(order by score desc) from score;
-- 分组排序
select score, row_number() over(partition by xxx order by score desc) from score;

-- dense_rank
-- 连续、不间断排名，当有相同的分数时，它们的排名结果是并列的，例：1、2、2、3、4
select score, dense_rank() over(order by score desc) from score;
-- 分组排序
select score, dense_rank() over(partition by xxx order by score desc) from score;

存储过程

-- 创建存储过程
create procedure pro_()
begin
  declare i int;
  declare user varchar(36);
  set i = 0;
  while i < 15000 do
    set user = uuid();
    insert into sys_user(user_id, username) values (user, user_id);
    insert into sys_org_user(relate_id, org_id, user_id) values (uuid(), org_id, user);
    set i = i+1;
  end while;
end;

-- 执行存储过程
call pro_;

-- 删除存储过程
drop procedure pro_;

Mybatis转义字符

转义符	符号	备注
&lt;	<	小于
&gt;	>	大于
&amp;	&	与
&apos;	'	单引号
&quot;	"	双引号