PL/SQL 基础用法

PL/SQL
基础用法

数据库相关

show databases;
show table status;
show open tables;
show create database leetcode;
create database my_test;
show databases;
drop database my_test;

数据库引擎

select distinct engine
from information_schema.TABLES;

create database learn;
use learn;
show tables;
drop table if exists tb_emp1;
create table tb_emp1
(
id int(11) comment '员工编号',
name varchar(25) comment '员工名称',
depId int(11) comment '部门',
salary float comment '工资'
);
describe tb_emp1;

/·栏位 就是columns
加一个栏位: ADD "栏位 1" "栏位 1 资料种类"
· 删去一个栏位: DROP "栏位 1"
· 改变栏位名称: CHANGE "原本栏位名" "新栏位名" "新栏位名资料种类"
· 改变栏位的资料种类: MODIFY "栏位 1" "新资料种类"/
alter table tb_emp1
drop column salary;
alter table tb_emp1
add column salary int(11) not null;

alter table tb_emp1
modify salary int(11);
alter table tb_emp1
modify salary int(11);
alter table tb_emp1
change depId depId int(2) not null;

显示所有约束

select *
from information_schema.TABLE_CONSTRAINTS
where TABLE_NAME = 'tb_emp1';
show create table tb_emp1;

charu

insert into tb_emp1(id, name, depId, salary)
values (1, 'xsj', 1, 0);
select *
from tb_emp1;
describe tb_emp1;

添加约束

alter table tb_emp1
add constraint tb_id primary key (id);
show create table tb_emp1;
update tb_emp1
set salary=2000;

alter table tb_emp1
add constraint tb_name_unique unique (name);
alter table tb_emp1
add constraint ch_salary check ( salary > 1800 );
alter table tb_emp1
drop check ch_salary;
alter table tb_emp1
add check (salary < 90000);

alter table 表名 add constraint 约束名 foreign key(关联字段) references 主表(关联字段)

/唯一性是index
not null 是约束
check 是check/

alter table tb_emp1
drop index tb_name_unique;

alter table tb_emp1
change salary salary float not null;

删除主键

alter table tb_emp1
drop primary key;

alter table tb_emp1
drop check tb_emp1_chk_1;

放在最后边

show index from tb_emp1;
show create table tb_emp1;

desc tb_emp1;

命令行里可以

show create table tb_emp1 \G;

alter table tb_emp1 rename to tb1;
show tables;
alter table tb1
change name name2 varchar(40) unique;
alter table tb1
drop index tb_name_unique;

放在最后边

show index from tb1;
show create table tb1;
desc tb1;

在某个字段后边插入一个新列

alter table tb1
add column name varchar(12) after name2;
alter table tb1
drop column name;
desc tb1;
show engines;

外键

create table if not exists tclass
(
gradeId int(3) not null auto_increment primary key,
name varchar(128) not null unique
);
insert into tclass
values (null, '1-1班'),
(null, '1-2班'),
(null, '1-3班');
select *
from tclass;
create table if not exists student
(
id int(5) not null primary key auto_increment,
name varchar(128) not null,
class int(3) not null,
age int(3),
constraint check ( age > 0 ),
key classId (class),
constraint f_class_id foreign key (class) references tclass (gradeId)
);
insert into student
values (null, 'llz', 3, 1);
insert into student
values (null, 'xsj', 1, 28);
insert into student
values (null, 'xt', 2, 25);
select *
from student;
alter table student
modify id int(11) unsigned auto_increment;

show index from student;
show create table student;
alter table student
drop foreign key f_class_id;
alter table student
drop index classId;

create index classId on student (class);

alter table student
add constraint f_class_id foreign key (class) references tclass (gradeId)
on delete cascade on update no action;
select *
from student;
select *
from tclass;

禁用外键

set foreign_key_checks = 0;
set foreign_key_checks = 1;
show tables;
select *
from tb1;
alter table tb1
modify salary float(4, 2);

update tb1
set tb1.salary=20.1272
where id = 1;

alter table tb1
drop check ch_salary;

alter table tb1
modify tax decimal(5, 4) default 0;
alter table tb1
modify depId int(2);
alter table tb1
modify id int(11) auto_increment;
select *
from tb1;
desc tb1;
insert into tb1 (id, name2, depid, salary, tax)
values (null, 'bjj2', 9999, 32.11, 2.343);

alter table tb1
add column y year;
select *
from tb1;
update tb1
set y='0'
where id = 1;
update tb1
set y=21
where id = 2;
update tb1
set y='21'
where id = 4;
update tb1
set y='2177'
where id = 5;
alter table tb1
add column date datetime;

select now();
insert into tb1 (date)
values (datediff(now(), 1));

select *
from tb1;

alter table tb1
add column time time;
select *
from tb1
where id = 8;
insert into tb1 (time)
values ('041123');

desc tb1;
select curdate();
select current_time();
select current_timestamp();
select now();

select pow(2, 8);

create table if not exists chars
(
id int not null primary key auto_increment,
c1 char(2),
c2 varchar(255),
t1 tinytext,
t2 text(65535)
);
desc chars;
insert into chars
values (null, '12', 'sldjkjdddd', 'ss', 'mediumtext');
select *
from chars;
select length(c1)
from chars;

show tables;
desc tb1;
alter table tb1
modify date datetime default now();
insert into tb1(tax)
values (2.3);
select *
from tb1;

select mod(10, 3);
select mod(-10, 3);
select rem(10, -3);
select -10 % 3;
drop function rem;
create function rem(x int, y int)
returns int
DETERMINISTIC
BEGIN
return x - sign(x / y) * floor(abs(x / y)) * y;
end;
select rem(10, 3);
select rem(-16, 3);
select mod(-16, -3);

运算符

select null = null n, 1 = 1 a, 1 = 2 c, 1 <=> 1 d, null <=> null f;
select 1 <= 2, 'goo' < 'goo0';
select isnull(null);
select power(1.05, 10);

select least(1, 2, 3, 4, -3);
select greatest(2, 3, 4, 422);
select null and null and 1, 2 and 1, 1 and 0;
select 1 or null, null or null, 0 or null, 0 or 1;
select 1 XOr 1, 2 xor 1, 0 xor 3, null xor 1, null xor null;
select binary 'a' = 'A', 'a' = 'A';

select 4 && 8, -2 || NULL, NULL XOR 0, 0 XOR 1, !2;

1,1,1,1,1,0,1

select 36 > 27, 15 >= 8, 40 < 50, 15 <= 15, NULL <=> NULL, NULL <=> 1, 5 <=> 5;

select rand(), rand(10);
select round(1.23), round(122.33332, -1);
select round(1.23), round(-1.3532, 2);

show function code rem ;

show variables like '%with-debug%';
select degrees(pi());
select radians(90);
select round(pi(), 25);
select length('我么') xx;
select char_length('我们') x;

为什么varcahr(6)的可以插入6个汉字，或者六个字符。

show tables;
select length(cl)
from chars;
alter table chars
add column cl varchar(6);
update chars
set cl='我们都是好孩'
where id = 1;
update chars
set cl='123456'
where id = 1;
select lpad('xsj', 1, '-');
select trim('ss' from 'ssswess');

select trim('-' from repeat('123-', 3));
select strcmp('xy', 'xz');
select locate('j', 'xsj');
select instr('xsj', 'j');
select from_unixtime(123);
select unix_timestamp();
select extract(year_month from now());
select date_add(now(), interval 1 second);
select date_add(now(), interval 1 minute);
select date_add(now(), interval 1 day);
select date_add(now(), interval 1 month);
select addtime(now(), 1), now();
select ifnull(2, 1);
select version();
select connection_id();
show processlist;
show full processlist;
show schemas;
select user();
select charset('');
select last_insert_id();
select md5('123456');
select md5('123456');
select sha('123456');
select sha('123456');
select sha2('123456', 224);
select conv(10, 10, 8);

lock

select get_lock('lock1', 10);
select is_free_lock('lock1');
select is_free_lock('lock2');
select is_used_lock('lock1');
select is_used_lock('lock2');

select release_lock('lock1');
show tables;

select benchmark(12, 'select * from salary');
select benchmark(10000, sha(rand()));

转换

select cast(100 as char(2));
select cast('2020-01-01' as date);
select current_user();

函数

select 18 / 5, 19 % 5, mod(19, 5), degrees(pi() / 4), pow(9, 4), round(pi(), 2);
select char_length('hello'), length('he llo'), length('我们') we;

字符串

select substr('Nice to meet you!', 9, 4),
repeat('Cheer', 3),
reverse('voo'),
concat_ws(' ', 'we', 'are', 'good');

日期

show processlist;
select SHA('MYSQL');
select MD5('MYSQL'), conv(100, 10, 8), format(3.21222, 4);

循环

with recursive cte(n) as (
select 1
union
select n + 1
from cte
where n < 8
)
select *
from cte;
show tables;
use leetcode;

类似connect by

select *
from t_areainfo;
with recursive father as (
select id, name, parentId from t_areainfo where id = 26
union all
select g.id, g.name, g.parentId from t_areainfo g
inner join father f on f.parentId = g.id
)
select group_concat( name order by id separator '-') from father;
用户权限

查询

select * from sales for update ;
select * from sales ;
show databases ;
show tables;

插入

insert into sales (id, year, country, product, profit)
values (20,2020,'china','computer',100);

distinct ,先distinct 然后在limit

select * from sales order by id limit 1,1;
select distinct year from sales limit 1,1;
select distinct year from sales limit 1 offset 1;

uuid

select upper(replace(uuid(),'-',''));
select uuid();

if null

select if(product='Phone','mobile',product) from sales;

设置id

update sales set ids=uuid();

dense，窗口函数

select country,profit, dense_rank() over (partition by country order by profit) rk,id
from sales;

select country,profit, row_number() over (partition by country order by profit) rk,id
from sales;

select country,profit, rank() over (partition by country order by profit) rk,id
from sales;

新建用户

select * from mysql.user;
select Host,User,authentication_string from mysql.user ;

use mysql;

use leetcode;

create user xsj@'localhost' identified by '123456';
create user xsj2@'localhost' identified by '123456';
create user xsj3@'localhost' identified by '123456';

flush privileges ;

直接插入数据库的方式新建用户，密码没有加密

insert into mysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject)
values ('localhost','shengjie','123456','','','');
flush privileges ;

SHOW GRANTS FOR 'root'@'localhost';

drop user xsj2@'localhost';
drop user xsj3@'localhost';
drop user shengjie@'localhost';
flush privileges;

给用户赋予权限

grant insert,update,select ON leetcode.*
To xsj@'localhost' identified by 'shengjie';
show databases ;
grant select, insert, update, delete on . to shengjie@'%' identified by '123456';

select host,User,authentication_string
from mysql.user where User like 'x%';

create user a1 identified by '123',a2 identified by '123';
drop user a1,a2;

create user xsj@'localhost' identified by '123456';
flush privileges;

with grant option 可以将自己的权限赋予别

grant select,update,insert on leetcode.* to xsj@'localhost' with grant option ;

这个是只能在本地连接把

create user leet@'localhost' identified by 'leet';
grant select,update,insert on leetcode.* to leet@'localhost' ;

修改密码

set password for xsj@'localhost' = 'shengjie';
flush privileges ;

set password for leet@'localhost' ='123456';
select host,User,authentication_string from mysql.user;

显示用户权限

show grants for leet@'localhost';
show grants for root@'%';

grant update,select,insert,delete on leetcode.* to leet@'localhost' with grant option ;
show grants for leet@'localhost';

删除权限

revoke update on leetcode.* from leet@'localhost';
flush privileges;
revoke all privileges on leetcode.* from leet@'localhost';
revoke grant option on leetcode.* from leet@'localhost';
revoke all privileges ,grant option from leet@'localhost';

时间日期

select now() +10;
select date_add(now(),interval 7 year );
select date_add(now(),interval 7 hour );
select datediff(date_add(now(),interval 7 day ),now());
select year(now());
select date (now());
select timestamp (now());
select time(now());
select date_format(now(),'%Y-%m-%d %H:%i:%S');

group，group_concat

select country,group_concat(product,'-',year) from sales group by country with rollup ;
select country,group_concat(product,'-',year) from sales group by country ;

select ifnull(year,1),ifnull(country,1),group_concat(year),group_concat(country) from sales group by year,country with rollup;

视图 关键词as

create view sales_group3 as select year,country,group_concat(country) from sales group by year, country;

select * from sales_group3;

describe sales_group;
show tables ;

drop view sales_group3;
select * from sales_group;

普通索引

create index sales_id on sales(id);

唯一索引

CREATE UNIQUE INDEX index_id ON sales(id);

create index country_4 on sales(country(4));

显示索引

show index from sales;
drop index index_id on sales;

EXPLAIN select * from sales where country like 'c%';
EXPLAIN select * from sales where id=2;
EXPLAIN select now();
select now();
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation_server';
select Host,User from mysql.user;

触发器

create trigger ids_trigger
before insert on sales
for each ROW
BEGIN
SET new.ids=REPLACE(UUID(),'-','');
END;

备份

use seasons;
show tables;
use leetcode;

select * from sales;

select * from sales into outfile 'my.txt';
SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'log_bin';
SHOW binary logs;
SHOW VARIABLES LIKE '%general%';
数据库约束

数据库相关

show databases ;
show table status ;
show open tables ;
show create database leetcode;
create database my_test;
show databases;
drop database my_test;

数据库引擎

select distinct engine from information_schema.TABLES;

create database learn;
use learn;
show tables;
drop table if exists tb_emp1;
create table tb_emp1(
id int(11) comment '员工编号',
name varchar(25) comment '员工名称',
depId int(11) comment '部门',
salary float comment '工资');
describe tb_emp1;

/·栏位 就是columns
加一个栏位: ADD "栏位 1" "栏位 1 资料种类"
· 删去一个栏位: DROP "栏位 1"
· 改变栏位名称: CHANGE "原本栏位名" "新栏位名" "新栏位名资料种类"
· 改变栏位的资料种类: MODIFY "栏位 1" "新资料种类"/
alter table tb_emp1 drop column salary;
alter table tb_emp1 add column salary int(11) not null;

alter table tb_emp1 modify salary int(11);
alter table tb_emp1 modify salary int(11);
alter table tb_emp1 change depId depId int(2) not null ;

显示所有约束

select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME='tb_emp1';
show create table tb_emp1;

charu

insert into tb_emp1(id,name,depId,salary)values (1,'xsj',1,0);
select * from tb_emp1;
describe tb_emp1;

添加约束

alter table tb_emp1 add constraint tb_id primary key (id);
show create table tb_emp1;
update tb_emp1 set salary=2000;

alter table tb_emp1 add constraint tb_name_unique unique(name) ;
alter table tb_emp1 add constraint ch_salary check ( salary>1800 );
alter table tb_emp1 drop check ch_salary;
alter table tb_emp1 add check(salary<90000);

alter table 表名 add constraint 约束名 foreign key(关联字段) references 主表(关联字段)

/唯一性是index
not null 是约束
check 是check/

alter table tb_emp1 drop index tb_name_unique;

alter table tb_emp1 change salary salary float not null ;

删除主键

alter table tb_emp1 drop primary key ;

alter table tb_emp1 drop check tb_emp1_chk_1;

放在最后边

show index from tb_emp1;
show create table tb_emp1;
存储过程

正则表达式

show tables;
create table if not exists reg
(
name varchar(100) not null
);
select *
from reg;
/insert into reg values ('xxs');
insert into reg values ('xxs,a,b,d,c,,ds,d,');
insert into reg values ('xxs238sd');
insert into reg values ('37943');
insert into reg values ('2.31');
insert into reg values ('2.31d');
insert into reg values ('2.2.1');/

2***d结尾的。

select *
from reg
where regexp_like(name, '^2.*d$');

不等于1,2,3

select *
from reg
where regexp_like(name, '[^123]');

select *
from reg
where regexp_like(name, '[^0-9.]');
select *
from reg
where regexp_like(name, '[a-z]+');
select *
from reg
where regexp_like(name, ',');
select *
from reg
where regexp_like(name, '2\..*?');
select name, regexp_like(name, '(2{1})')
from reg;
select *
from reg
where regexp_like(name, '[0-9]{2,3}');

select name, regexp_instr(name, '[,]+', 1, 2)
from reg;

select name, regexp_replace(name, 'x', 'a')
from reg;

select 'Bn' REGEXP '^Ba*n';

select 'pix' REGEXP '^(pi|apa)$';

-> 0（表示不匹配）

select substr('xsjsss', 3, 2);

select name
from reg;

select *
from reg
where regexp_like(name, '2|,');
select name, regexp_substr(name, '[^,]+', 1, 1)
from reg;
select *
from reg;

select *
from reg
where regexp_like(name, '.2.d$');

循环查找

DROP TABLE IF EXISTS t_areainfo;
CREATE TABLE t_areainfo
(
id int(11) NOT null AUTO_INCREMENT,
level int(11) DEFAULT '0',
name varchar(255) DEFAULT '0',
parentId int(11) DEFAULT '0',
status int(11) DEFAULT '0',
PRIMARY KEY (id)
) ENGINE = InnoDB
AUTO_INCREMENT = 65
DEFAULT CHARSET = utf8;

select *
from t_areainfo
where parentId = 4
or id = 4
order by id, parentId;

通过当前id获取父类

DROP FUNCTION IF EXISTS queryChildrenAreaInfo;

CREATE FUNCTION queryChildrenAreaInfo(areaId INT)
RETURNS VARCHAR(4000)
DETERMINISTIC
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp = '$';
SET sTempChd = cast(areaId as char);
WHILE sTempChd is not NULL
DO
SET sTemp = CONCAT(sTemp, ',', sTempChd);
SELECT group_concat(id) INTO sTempChd FROM t_areainfo where FIND_IN_SET(parentId, sTempChd) > 0;
END WHILE;
return sTemp;
END;

select concat('a',',','b');

select cast(1 as char);

创建存储过程。

select queryChildrenAreaInfo(4);

select *
from t_areainfo
where FIND_IN_SET(id, queryChildrenAreaInfo(4));

drop function if exists test;
create function test()
returns varchar(100)
DETERMINISTIC
begin
return (select group_concat(name) name from reg where name like 'x%');
end;

select test() name;

创建一个存储函数

drop function if exists test_pro;
create function test_pro()
returns int
DETERMINISTIC
BEGIN
declare ids int default 1;
select id into ids from sales where id = 2;
set ids = 10;
return ids;
end;
select test_pro() ids;

loop循环

drop function if exists test_add;

create function test_add(max int)
returns int
DETERMINISTIC
BEGIN
declare num int default 0;
declare sum int default 0;
add_num:
loop
set num = num + 1;
set sum = sum + num;
if num >= max then
leave add_num;
end if;
end loop add_num;
return sum;
end;

select test_add(12);

for 循环

create function findParent(chdId int)
returns varchar(4000)
DETERMINISTIC
BEGIN
declare allId varchar(4000);
declare nowIds varchar(4000);
set allId = '$';
set nowIds = cast(chdId as char);
while nowIds is not null
do
set allId = concat(allId, ',', nowIds);
select group_concat(parentId) into nowIds from t_areainfo where find_in_set(id, nowIds);
end while;
return allid;
end;
select findParent(26);
select * from t_areainfo;
select replace(group_concat(name),',','-') addr from t_areainfo where find_in_set(id,findParent(5))
order by id;
其他
show tables;
select '1' where find_in_set('c','a,b,n');
insert into reg values (0);
select * from reg;
select * from reg where name!='0';
select cast('2.2.3' as float );
select locate('a','bsada');
select replace(' sldj sd ',' ','');
select CHAR_LENGTH('sld中文');
select character_length('sl中午');
select concat('a','b');
select concat_ws('-','a','b','c','d');
select format(2932472.13,1);
select round(323423.323212,4);
select left('foijfldsfj',3);
select concat('a',space(10),'w');
select curdate();
select curtime();
select current_timestamp();
select adddate(now(),-7);
select addtime(now(),100);
select dayofweek(now());
select dayofweek(date('2020-10-10'));
select date('2020-01-10 12:12:12');
select sysdate();
select CONNECTION_ID();
select database();
select current_user();
select LAST_INSERT_ID();

标准格式化

select minute(date_format('2020-01-01 23:10:12','%Y-%m-%d %T'));
select date_format('2020-01-01 23:10:12','%Y-%m-%d %T');
select date_format(now(),'%Y-%m-%d %r');
1.mod 和rem的区别。

首先先说fix和floor取整的区别。floor取整时向负无穷取，fix向零取。也就是当为正数时两个没有区别。负数时，fix比floor大1。

再说mod=floor(x/y)，rem=fix(x/y)。

一、left join

1、left join on

on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、left join on and

（1）如果and语句是对左表进行过滤的，那么不管真假都不起任何作用。

（2）如果and语句是对右表过滤的，那么左表所有记录都返回，右表筛选以后再与左表连接返回。

3、left join on where

where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了。

（1）此时相当于inner join on

（2）此时on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。

4、inner join on and 和 inner join on where

无区别，不管是对左表还是右表进行筛选，on and 和 on where都会对生成的临时表进行过滤。

二、group by

select xxx_name, count(*) as counter
from table_y
where where_condition -- 分组前过滤条件
group by xxx_name --分组名称
having having_condition -- 对分组后的数据进行过滤，因为分组后的数据都是与分组有关的。
order by zzz
分布式
1.启动mysql 容器
docker run --name mysql-lates --restart always
-v /etc/timezone:/etc/timezone
-v /etc/localtime:/etc/localtime
-p 3306:3306 -p 33060:33060
-e MYSQL_ROOT_PASSWORD=shengjie
-e MYSQL_ROOT_HOST='%'
-e LANG=C.UTF-8 -d mysql:latest
创建数据库

create database seasons
创建数据表

是否需要用powerdesigner设计呢？
2.用idea 连接mysql
其实在idea 里写代码挺方便的。
3.数据库设置
3.1 设置时区
查看linux时区
date -R

设置时区
tzselect
然后一步步选择就好了。

设置好tzselect后，一定要重写/etc/localtime
rm /etc/localtime
ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

进入容器

docker exec -it mysql8 bash

到目标目录

cd /etc/mysql

追加到文件末尾

echo "default-time-zone = '+08:00'">>my.cnf

重启mysql

然后连接上mysql用select NOW(); 查看是否设置成功

cd /etc/mysql
cp my.cnf my.cnf.back
echo "character-set-server=utf8">> my.cnf

停止mysql服务

mysqladmin -uroot -proot shutdown

启动

mysqld

a、注意理解mysqld与mysql。mysqld为mysql服务器或者称之为服务器上的实例，用于提供客户端访问。mysql为客户端访问管理工具。
b、可以通过三种方式启动mysql服务器(mysqld，mysqld_safe，service mysql start)
c、对于mysqld_safe，service mysql start方式，总是通过调用mysqld_safe来启动mysqld进程，以防止mysqld意外宕掉而提供重启服务。
d、可以通过mysqladmin shutdown以及service mysql stop来终止mysql服务器
备份
导出所有数据库
mysqldump -u username -P --all-databases>filename.sql

导出指定数据库

mysqldump -u username -P -databases d1 d2 d3>filename.sql

导出某个表

mysqldump -u username -P d1 t1,t2>filename.sql

daoru all

mysql -uroot -p < ss.sql
msyql -uroot -p databsesname <ss.sql

my.cnf设置错误日志路径
log-error=dir/{filename}

查询错误日志路径

SHOW VARIABLES LIKE 'log_error';

删除错误日志

mysqladmin -uroot -p flush-logs

查看日志

mysqlbinlog filename.number
4.一个可以解决市区和编码问题

Docker file for date and locale set

VERSION 0.0.3

Author: bolingcavalry

基础镜像

FROM centos:7

作者

MAINTAINER BolingCavalry zq2599@gmail.com

定义时区参数

ENV TZ=Asia/Shanghai

设置时区

RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo '$TZ' > /etc/timezone

安装必要应用

RUN yum -y install kde-l10n-Chinese glibc-common

设置编码

RUN localedef -c -f UTF-8 -i zh_CN zh_CN.utf8

设置环境变量

ENV LC_ALL zh_CN.utf8
5、配置备份
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

Custom config should go here

!includedir /etc/mysql/conf.d/
default-time-zone = '+08:00'
character-set-server=utf8
server-id=152
log-bin=mysql-bin
binlog-do-db=oauth
查看masate状态
show master status;
查看日志内容
show binlog events in ''

-- 恢复数据方法如下：
mysqlbinlog mysql-bin.000009 --start-position 154 --stop-position 755 | mysql -uroot -p mytest

下面是binlog日志恢复中一些常用的参数

--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间

--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样

--start-position：从二进制日志中读取指定position 事件位置作为开始。

--stop-position：从二进制日志中读取指定position 事件位置作为事件截至