MySQL的一些记录

MySQL学习记录

1. 权限

MySQL权限管理的两个阶段:连接检查和权限检查,前者就是指定哪些用户可以进行连接(具体可以理解为哪些IP可以连接),后者是用户对哪些库或者表有具体的操作权限。

配置好环境变量,将MySQL的服务添加到Windows服务中,基本登陆命令

mysql -h localhost -u root -p

关于权限的配置,一般是给指定的ip用户提供能满足需求的最小权限即可,

grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';

比如:

grant all privileges on *.* to jack@'localhost' identified by "jack" with grant option;

【注意】上述的创建用户并分配权限的语句在MySQL8.0.12版本中运行会出现语法错误,但是官方文档也是这么写的,但是实际操作过程中会遇到如下的语法错误:

mysql> GRANT ALL privileges ON *.*  TO 'jack'@'localhost';
ERROR 1410 (42000): You are not allowed to create a user with GRANT

查阅发现,创建用户和分配权限的行为分开操作可解决:

# 先创建用户tom,密码为tom
mysql> create user 'tom'@'loaclhost' identified by 'tom';
# 再赋予具体表glodon_test权限
mysql> GRANT ALL privileges ON glodon_test TO 'tom'@'localhost';
Query OK, 0 rows affected (0.08 sec)
# 刷新权限即可生效
mysql> flush privileges;

【注意】在后续过程中,使用上述步骤赋予权限时还是出现ERROR 1410 (42000): You are not allowed to create a user with GRANT错误信息。

对于所有库的表权限赋予还是无法解决,出现如下信息:

mysql> GRANT ALL privileges ON *.* TO 'jack'@'localhost';
ERROR 1410 (42000): You are not allowed to create a user with GRANT

另外对于show grants的使用也存在一些疑问,我以当前用户root身份只能查看自己的权限,无法查看之前创建的jack用户权限,如下:

mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                  |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

查询jack用户时出现如下信息:

mysql> SHOW GRANTS FOR 'jack'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'jack' on host 'localhost'

问题解决:jack用户只是创建出来,并没有赋予权限,所以出现上面的问题,之后创建用户tom并授权,使用root用户登录可以查看tom用户的权限;

在删除用户时,出现下面的问题,第一次删除成功,第二次删除失败:

mysql> drop user 'tom'@'localhost';
Query OK, 0 rows affected (0.17 sec)

# 'test'@'localhost'用户确实存在
mysql> drop user 'test'@'localhost';
ERROR 1396 (HY000): Operation DROP USER failed for 'test'@'localhost'

还发现如下的问题drop无法删除,使用普通的delete方法却可以正常删除:

mysql> drop user 'test'@'localhost';
ERROR 1396 (HY000): Operation DROP USER failed for 'test'@'localhost'
mysql> DELETE FROM mysql.user WHERE Host='loaclhost' AND User='test';
Query OK, 1 row affected (0.12 sec)

更改密码时,mysql>update mysql.user set password=password(’123456′) where User=’test1′ and Host=’localhost’;语法在8.X版本中已经无法使用,更改密码使用下面的语句,但是发现只能修改当前用户的密码(并不能修改其他用户的密码):

# 修改root@localhost用户密码为root1
ALTER USER root@localhost IDENTIFIED BY 'root1';

在做完权限的基本测试时,尝试重新配置了5.7.22 MySQL Community Server (GPL)成功,PPT上的权限部分重新语句重新执行一边,通过。

创建拥有某个测试数据库的插入权限的账户:

mysql> grant insert on glodon_test.* to test@'localhost' identified by "test" with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from user;
ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'user'
mysql> insert into user (id,name,age)values (20,'insert',20);
Query OK, 1 row affected (0.03 sec)

总而言之赋权的基本命令形式为(根据具体情况赋予不同用户对不同数据库的不同表的不同权限):

GRANT 权限 ON 数据库名.表名 TO '用户名'@'登陆IP';

撤销权限的语法为:

revoke 权限 on 数据库名.表名 from '用户名'@'登陆IP';

数据库名.表名可以使用通配符写成*.*的形式表示任意数据库的任意表,登陆IP可以使用%来表示任意IP地址,具体有哪些权限可以参照下面官方给出的MySQL5.7版本具体权限列表:

这里写图片描述

在密码安全方面,在MySQL4.1后引入了新版哈希发来对密码加密,使用SELECT password('密码')的命令可以查看对应的加密形式,4.1版本之前是16位,之后的新版本是41位,比如下面的:

mysql> select password('mypass');
+-------------------------------------------+
| password('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

在更改密码的时候有如下的方法:

# 方法1
set password for 用户名@localhost = password('新密码');

# 方法2
mysqladmin -u用户名 -p旧密码 password 新密码;

# 方法3
mysql> use mysql;
mysql> update user set password=password('新密码') where user='用户名' and host='登陆IP';
mysql> flush privileges;

# 方法4
ALTER USER 用户名@IP地址 IDENTIFIED BY '新密码';

【注意】方法2更改时在用户登陆的状态下是无法修改密码的,必须在登出MySQL的情况下才可用(忘记登陆密码可以用这个方法解决)。方法3中只适用于老版本的MySQL(新版MySQL中的user表中已经不再直接存放password字段)。

忘记密码的解决方法:

  1. 关闭正在运行的MySQL服务(net stop mysql)。
  2. 打开DOS窗口,转到mysql\bin目录。(如果已经配置了环境变量可省略)
  3. 输入mysqld --skip-grant-tables(启动MySQL服务的时候跳过权限表认证)回车。
  4. 再开一个DOS窗口(因为刚才那个DOS窗口已经不能动了),转到mysql\bin目录。
  5. 输入mysql回车,如果成功,将出现MySQL提示符 >。
  6. 连接权限数据库: use mysql;
  7. 改密码:update user set password=password("123") where user="root";(别忘了最后加分号) 。
  8. 刷新权限(必须步骤):flush privileges;
  9. 退出 quit
  10. 注销系统,再进入,使用用户名root和刚才设置的新密码123登录。

2. 基本语法

 SQL分为两部分:数据操作语言(DML)、数据定义语言(DDL),下面是展示了一个数据库的基本创建,包含了基本语法和其作用注解以及一些使用的注意点,自己本地机器上的5.7.22 MySQL Community Server (GPL)版本运行全部通过。

# 创建数据库
CREATE DATABASE db_test;

USE db_test;

# 创建表
CREATE TABLE student (
	s_id INT NOT NULL auto_increment,
	s_name VARCHAR (50) NOT NULL,
	s_age INT (10) NOT NULL,
	# 创建主键
	PRIMARY KEY (s_id)
);

# 添加
INSERT INTO student (s_name, s_age) VALUES ('jack1', 21);
INSERT INTO student (s_name, s_age) VALUES ('jack2', 22);
INSERT INTO student (s_name, s_age) VALUES ('jack3', 23);
INSERT INTO student (s_name, s_age) VALUES ('jack4', 24);
INSERT INTO student (s_name, s_age) VALUES ('jack1', 211);
INSERT INTO student (s_name, s_age) VALUES ('jack2', 222);
INSERT INTO student (s_name, s_age) VALUES ('jack3', 233);
INSERT INTO student (s_name, s_age) VALUES ('jack4', 243);

# 删除
DELETE FROM student WHERE s_id=8;

# 更新
UPDATE student set s_name='update',s_age=25 WHERE s_id=1;  

# 查询
SELECT * FROM student;

# 根据列属性字段去重查询
SELECT DISTINCT s_name FROM student;

# 模糊查询LIKE,%表示匹配任意多的任意字符,_匹配单个字符
SELECT s_name FROM student WHERE s_name LIKE '_a%1%';

# 排序(默认升序ASC,也可手动指定降序DESC)
SELECT * FROM student ORDER BY s_name DESC;

# 分页查询,LIMIT 开始的索引,每页显示元素的个数,注意索引从0开始
SELECT * FROM student LIMIT 0,4;

# MySQL本身不支持TOP,order by和limit组合可以解决,比如最小年纪的前4个
SELECT * FROM student ORDER BY s_age LIMIT 0,4;

# 区间判断查询BETWEEN X AND XX
SELECT * FROM student WHERE s_age BETWEEN 20 AND 25;


# 创建成绩表,使用studnet表中的s_id作为成绩表的外键和student关联
# 创建外键,将c_sid外链到studnet表中的s_id字段
CREATE TABLE reposrt_card (
	c_id INT NOT NULL PRIMARY KEY,
	c_course VARCHAR (50),
	c_ score INT (10),
	c_sid INT (10) NOT NULL,
	FOREIGN KEY (c_sid) REFERENCES student(s_id) ON DELETE CASCADE
);

CREATE TABLE reposrt_card (
	c_id INT NOT NULL auto_increment,
	c_course VARCHAR (50) NOT NULL,
	c_score INT (10),
	c_sid INT (10) NOT NULL,
	# 创建主键
	PRIMARY KEY (c_id),
	# 创建外键,将c_sid外链到student表的s_id字段,ON指明外键删除时的行为为联动删除,
	# CASCADE:联动删除,RESTRICT:必须先删除子表数据才能删除主表
	FOREIGN KEY (c_sid) REFERENCES student(s_id) ON DELETE CASCADE
);

# 内连接查询,同时满足ON后面条件的数据才会显示
SELECT * FROM student INNER JOIN reposrt_card ON student.s_id = reposrt_card.c_sid;

# 左连接查询,左表中的都有,右表中只显示满足ON后面条件的数据
SELECT * FROM student LEFT JOIN reposrt_card_copy ON student.s_id = reposrt_card_copy.c_sid;

# 右连接查询,右表中的都有,左表中只显示满足ON后面条件的数据
SELECT * FROM student RIGHT JOIN reposrt_card_copy ON student.s_id = reposrt_card_copy.c_sid;

# 合并左表和右表,并且去重
SELECT s_id FROM student UNION SELECT reposrt_card_copy.c_sid FROM reposrt_card_copy; 


# 表复制,这里一定先创建一个和源表一样结构的复制表
CREATE TABLE student_copy (
	s_id INT NOT NULL auto_increment,
	s_name VARCHAR (50) NOT NULL,
	s_age INT (10) NOT NULL,
	# 创建主键
	PRIMARY KEY (s_id)
);

# 将表student复制到student_copy中
insert into student_copy select * from  student;

# 创建索引
CREATE INDEX StudentIndex ON student (s_name, s_age);

# 删除索引
DROP INDEX StudentIndex ON student;

# 添加表的字段,为student添加一个date类型的birthday字段
ALTER TABLE student ADD birthday date;

# 删除表的字段,
ALTER TABLE student DROP COLUMN birthday;

# 创建视图
CREATE VIEW student_view AS SELECT * FROM student WHERE s_id = 1;

# 删除视图
DROP VIEW student_view;

# 判断字段属性是否为空,用Is Null , is not null,不能用等号,如果'birthday'为空返回'asc'
SELECT IFNULL('birthday','asc') FROM student;

# 删除表
# DROP TABLE student;

# 删除数据库
# drop DATABASE db_test;

MySQL的语法也是支持正则表达式的,不一一举例,用到查表即可,详细如下:

这里写图片描述

3. 事务

 MySQL中的事务必须满足ACID:原子性、一致性、隔离性、持久性。关于事务的操作,MySQL主要有如下的几种命令:

  • BEGIN/START TRANSACTION:显式地开启一个事务;
  • COMMIT/COMMIT WORKCOMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的;
  • ROLLBACK/ROLLBACK WORK:回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
  • SAVEPOINT identifier:允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT;
  • RELEASE SAVEPOINT identifier:删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
  • ROLLBACK TO identifier:把事务回滚到标记点;
  • SET TRANSACTION:用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSERIALIZABLE

 下面是几种简单用法的举例:

# 显式的开启事务
BEGIN;
DELETE FROM student_copy WHERE s_id=7;
# 提交事务,删除
COMMIT;

# 禁止自动提交
SET autocommit=0;
# 执行查询
DELETE FROM student_copy WHERE s_id=6;
# 在自动提交关闭后,查询后必须手动COMMIT,否则更新不能真正落实到数据中
COMMIT;

# 开启自动提交
SET autocommit=1;
DELETE FROM student_copy WHERE s_id=5;

# 显式的开启事务
BEGIN;
DELETE FROM student_copy WHERE s_id=4;
# 回滚事务,前面做的行为不落实到数据库中
ROLLBACK;

4. 临时表

 临时表在保存一些临时数据时是非常有用的,但它只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。临时表比较神奇,创建临时后用户是无法看到这个临时表的,哪怕使用SHOW TABLES;命令也是查询不到临时表名的,但是却可以对临时表进行增、删、改、查的操作,通过select语句可以看到临时表中的内容。可以用下方的命令得到验证:

# 创建临时表
CREATE TEMPORARY TABLE db_tem (
	s_id INT NOT NULL auto_increment,
	s_name VARCHAR (50) NOT NULL,
	s_age INT (10) NOT NULL,
	PRIMARY KEY (s_id)
);

# 不显示db_tem表
SHOW TABLES;

INSERT INTO db_tem (s_name, s_age) VALUES ('jack1', 21);

# 可以看到db_tem表中刚插入的数据
SELECT * FROM db_tem;

DROP TABLE db_tem;

5. 表复制

&esmp;前面在基本语法小结中已经对表复制有部分涉及:

# 表复制,这里一定先创建一个和源表一样结构的复制表
CREATE TABLE student_copy (
	s_id INT NOT NULL auto_increment,
	s_name VARCHAR (50) NOT NULL,
	s_age INT (10) NOT NULL,
	# 创建主键
	PRIMARY KEY (s_id)
);

# 将表student复制到student_copy中
insert into student_copy select * from  student;

可以发现我们是将student创建表的语句复制过来改了表名然后创建了与student表结构一致的表,最后通过insert into student_copy select * from student;将内容复制进行,前面创建同样结构的步骤还可以写作:

# 创建一个和reposrt_card结构一致的表reposrt_card_copy2
CREATE TABLE reposrt_card_copy2 LIKE reposrt_card;

然后再insert into这就很方便了,但是这里注意CREATE TABLE ... LIKE ...;可以将字段名和类型可以带到新表中,甚至索引也可以带过去,但是外键的关联关系是带不进新建的表中的,比较推荐这种方法。

 当然上面的表复制是分两步走:创建新表–>复制数据到新表中。也可以在创建表的同时复制数据,如下:

# 复制表student到表student_new2中
CREATE TABLE student_new2 AS (SELECT * FROM student);

用上面的语句可以选择具体字段进行复制而不全部复制,比如只复制student表中的idname属性:

CREATE TABLE student_new2 AS (SELECT s_name,s_age FROM student);

还可以重新指定复制表中的字段名:

# 源表student中的s_id、s_name字段名复制到新表中名字为sid、sname
CREATE TABLE student_new5 AS (SELECT s_id as sid, s_name as sname FROM student);

在进行部分数据的复制还可以加入某些规则,如:

CREATE TABLE student_new7 AS (SELECT * FROM student WHERE LEFT(s_name,1) = 'u' );

其中LEFT(s_name,1) = 'u'表示从从左开始截取字符串,即:left(被截取字符串, 截取长度),从左截取一个字符如果是u就复制到表中,否则不复制该数据。

还可以手动加入id,比如下面的:

# 将student表复制到表student_new8中并额外加入一个主键id,自增类型
CREATE TABLE student_new8 (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY ) AS (SELECT * FROM student);

6. MySQL其他的一些功能

 主要有如下命令查看相应的状态:

# 查看MySQL的版本信息
SELECT VERSION();
# 查看用户创建的数据库
SELECT DATABASE();
# 查看用户
SELECT USER();
# 查看MySQL的运行状态
SHOW STATUS
# 查看MySQL的系统变量
SHOW VARIABLES

 序列常接触的有自增,对于这一块有一个序列重置的点,对一张拥有自增序列的表,一旦中间删除了某些数据,其自增的id属性将会非常混乱,那么可以通过先删除自增序列然后再添加的方式将会重新自增,不过该操作要非常小心,如果在删除的同时又有新记录添加,有可能会出现数据混乱。下面是一个简单金的删除再添加的过程:

ALTER TABLE student_new8 DROP id;
ALTER TABLE student_new8 ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);

除此之外,还可以设置序列的开始值:ALTER TABLE student_new8 AUTO_INCREMENT = 100;elect LAST_INSERT_ID( ) from student_new8;可以动态监控同一条连接的更新的id。

 最后MySQL还具备数据导出的功能,使用

SELECT * FROM student_new8 INTO OUTFILE '‪C:/Users/liuwg-a/Desktop/test.txt';

即可将student_new8中的数据导出到后面指定的目录下(注意是斜杠,不是反斜杠)。

但是在导出过程中出现如下错误信息:

[Err] 1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

MySQL中secure-file-priv属性默认为null,即禁止文件导出的功能,需要在配置文件中将其置空,重启MySQL服务即可,配置文件中加入:

[mysqld_safe]
[mysqld]
secure_file_priv=''

7. MySQL的基本数据类型和常用函数

 关于基本数据类型,主要就是记忆几种常用的,具体如下面的几张表:

这里写图片描述
这里写图片描述
这里写图片描述

这里需要通常写完数据类型后会在后面跟上括号里面写上一个数字,比如Int(Size),是指显示的长度,大于该长度的直接显示,小于该长度对自动补成Size长度,比如Int(3),那么该类型下有数据:1101001000,那么将会是0010101001000

MySQL中还有一些常用的函数:

函数说明
MID(ColumnName, Start , [Length])从字符串ColumnName的第start个元素(包含)开始截取,截取length个字符。
AVG(DISTINCT expression)计算expression这一列属性的平均值
COUNT(expression)计算指定expression列属性不为空的行数,如果指定为*或者1那么将对所有数据计数(包括null)
MAX/MIN(DISTINCT expression)函数返回一组值中的最大/小值
SUM(DISTINCT expression)函数用于计算一组值或表达式的总和
LENGTH(str)获取以字节为单位的字符串长度
ROUND(X)/ROUND(X,D)返回x舍入到最接近的整数。如果有第二个参数,返回x四舍五入至第D位小数点
NOW()YYYY-MM-DD HH:MM:DDYYYYMMDDHHMMSS.uuuuuuu格式的字符串或数字返回配置的时区中的当前日期和时间
DATE_FORMAT(date,format)将日期值date格式化为特定格式format,具体写法下面有说明
UCASE/LCASE(str)将str字符更改为大/小写
GROUP BY根据一个或多个列对结果集进行分组,在分组的列上可以使用COUNT,SUM,AVG等函数
HAVINGSELECT语句中使用HAVING子句来指定一组行或聚合的过滤条件,通常与GROUP BY子句一起使用,以根据指定的条件过滤分组,如果省略GROUP BY子句,则HAVING子句的行为与WHERE子句类似

【注意】HAVING子句将过滤条件应用于每组分行,而WHERE子句将过滤条件应用于每个单独的行。具体用法可参见HAVING具体用法。最后就是DATE_FORMAT函数中的第二个参数format写法有很多,下面一些写法的说明和一些常用写法:

限定符含义
%a三个字符缩写的工作日名称,例如:Mon, Tue, Wed,等
%b三个字符缩写的月份名称,例如:JanFebMar
%c以数字表示的月份值,例如:1, 2, 3…12
%D英文后缀如:0th, 1st, 2nd等的一个月之中的第几天
%d如果是1个数字(小于10),那么一个月之中的第几天表示为加前导加0, 如:00, 01,02, …31
%e没有前导零的月份的日子,例如:1,2,… 31
%f微秒,范围在000000..999999
%H24小时格式的小时,前导加0,例如:00,01..23
%h小时,12小时格式,带前导零,例如:01,02 … 12
%I%h相同
%i分数为零,例如:00,01,… 59
%j一年中的的第几天,前导为0,例如,001,002,… 366
%k24小时格式的小时,无前导零,例如:0,1,2 … 23
%l12小时格式的小时,无前导零,例如:0,1,2 … 12
%M月份全名称,例如:January, February,…December
%m具有前导零的月份名称,例如:00,01,02,… 12
%pAMPM,取决于其他时间说明符
%r表示时间,12小时格式hh:mm:ss AMPM
%S表示秒,前导零,如:00,01,… 59
%s%S相同
%T表示时间,24小时格式hh:mm:ss
%U周的第一天是星期日,例如:00,01,02 … 53时,前导零的周数
%u周的第一天是星期一,例如:00,01,02 … 53时,前导零的周数
%V%U相同,它与%X一起使用
%v%u相同,它与%x一起使用
%W工作日的全称,例如:Sunday, Monday,…, Saturday
%w工作日,以数字来表示(0 = 星期日,1 = 星期一等)
%X周的四位数表示年份,第一天是星期日; 经常与%V一起使用
%x周的四位数表示年份,第一天是星期日; 经常与%v一起使用
%Y表示年份,四位数,例如2000,2001,…等。
%y表示年份,两位数,例如00,01,…等。
%%将百分比(%)字符添加到输出

常用写法有:

DATE_FORMAT字符串格式化日期
%Y-%m-%d2017/4/30
%e/%c/%Y4/7/2013
%c/%e/%Y7/4/2013
%d/%m/%Y4/7/2013
%m/%d/%Y7/4/2013
%e/%c/%Y %H:%i4/7/2013 11:20
%c/%e/%Y %H:%i7/4/2013 11:20
%d/%m/%Y %H:%i4/7/2013 11:20
%m/%d/%Y %H:%i7/4/2013 11:20
%e/%c/%Y %T4/7/2013 11:20
%c/%e/%Y %T7/4/2013 11:20
%d/%m/%Y %T4/7/2013 11:20
%m/%d/%Y %T7/4/2013 11:20
%a %D %b %YThu 4th Jul 2013
%a %D %b %Y %H:%iThu 4th Jul 2013 11:20
%a %D %b %Y %TThu 4th Jul 2013 11:20:05
%a %b %e %YThu Jul 4 2013
%a %b %e %Y %H:%iThu Jul 4 2013 11:20
%a %b %e %Y %TThu Jul 4 2013 11:20:05
%W %D %M %YThursday 4th July 2013
%W %D %M %Y %H:%iThursday 4th July 2013 11:20
%W %D %M %Y %TThursday 4th July 2013 11:20:05
%l:%i %p %b %e, %Y7/4/2013 11:20
%M %e, %Y4-Jul-13
%a, %d %b %Y %TThu, 04 Jul 2013 11:20:05

8. MySQL的存储过程和触发器以及实用优化

 存储过程略过,触发器这一块主要记录一下其使用,创建触发器的语法:

CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
	...
END;
  • trigger_name:即触发器的名字,需要自己取名,触发器名称应遵循命名约定[trigger time][table name][trigger event],例如before_employees_update;
  • trigger_time:即表示在事件之前或之后触发,有两个选用值BEFORE或者AFTER
  • trigger_event:即触发事件,触发该触发器的具体事件,可以是INSERTUPDATEDELETE,注意:触发器只能由一个事件调用。要定义由多个事件调用的触发器,必须定义多个触发器,每个事件一个触发器;
  • table_name:指定触发器与关联的特定表名;
  • ...:定义触发器逻辑,即触发器被触发了要搞什么事情;

举一个简单的例子:

CREATE TRIGGER insertigger_student_beforeinsert BEFORE INSERT ON student FOR EACH ROW
BEGIN
	INSERT INTO reposrt_card (
		c_id,
		c_course,
		c_score,
		c_sid
	)
	VALUES (90, 'jk', 90, 4);
END;

即在student表上创建了一个由insert触发的前置触发器,如果student表中将要插入新纪录,那么就先在reposrt_card表中插入一条(90, 'jk', 90, 4)记录,然后再执行student表中的插入操作。

 实际使用中注重性能的存在一些优化点(摘自PPT):

  • 查询频繁的列需要建立索引;
  • 少用类似select * from t where xx is null;
  • 尽量避免在where子句中使用!=<>操作符;
  • 尽量避免在where子句中使用or来连接条件;
  • 尽量避免使用innot in,否则会导致全表扫描;
  • 连续的数值,能用between就不要用in
  • 尽量用Exits代替In,(Exits会使用索引,In不会使用索引);
  • like ‘%abc%’不使用索引;
  • 尽量避免在where子句中对字段进行表达式操作,如select id from t where num/2 = 100select id from t where num = 100*2
  • 避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描,比如select id from t where datediff(day,createdate,’2005-11-30′) = 0select id from t where name like 'abc%' select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1‘
  • 对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差;
  • 尽量用Count(1)代替Count(*) 两个都是返回所有的;
  • 索引列最好不要超过6个;
  • 尽量使用数字类型代替字符类型(数字类型只比较一次,字符类型逐个字符对比);
  • 尽可能的使用varchar/nvarchar 代替 char/nchar,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些;
  • 任何地方都不要使用select * from t,用具体的字段列表代替*,不要返回用不到的任何字段;
  • 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写;
  • 尽量避免大事务操作,提高系统并发能力;
  • 尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

9.MySQL的数据库管理操作

9.1 数据库的备份及还原

 MySQL提供了备份数据库的工具mysqldump,在登出的状态,可以使用如下的命令备份:

mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
  • [username]:有效的MySQL用户名。
  • [password]:用户的有效密码。 请注意,-p和密码之间没有空格,默认空格后面就是待备份的数据库名,当然也不建议显示的在命令行中去写密码;
  • [database_name]: 要备份的数据库名字;
  • [dump_file.sql]:要生成的转储文件。

具体的备份案例如下:

1.备份某一个数据库:备份db_test到db_test_back.sql文件中;

mysqldump -u root -h localhost -p db_test > D:\Progra~2\MySQL\mysql-5.7.22-winx64\dbs_back\db_test_back.sql

2.备份某一个数据库的某一张表:备份db_test数据库中的student表到db_test_back.sql文件中;

mysqldump -u root -h localhost -p db_test student > D:\Progra~2\MySQL\mysql-5.7.22-winx64\dbs_back\db_test_back2.sql

3.备份多个数据:在-p后面追加--databases然后后面可以指定多个待备份的数据库名(数据库名之间使用空间做分割),比如备份db_test和db_test2两个数据库:

mysqldump -u root -h localhost -p --databases db_test db_test2 > D:\Progra~2\MySQL\mysql-5.7.22-winx64\dbs_back\db_test_back3.sql

4.备份所有数据库:和上一个类似,在-p后面追加--all-databases即可,指定输出的转出文件名即可,比如:

mysqldump -u root -h localhost -p --all-databases > D:\Progra~2\MySQL\mysql-5.7.22-winx64\dbs_back\db_test_back_all.sql

【注意】一定不要在最后习惯性的像SQL语句加上;,否则会报错!,另外关于windows中路径存在空格的情况处理,可以“取前6个字符按+~+数字”的形式得以解决,比如我这里的Program Files就可以使用Progra~2简写方式来代替它。

 通过以上的步骤我们已经完成数据库的备份工作了,再看一下数据库的还原,将转储文件db_test_back.sql还原到db_test_back数据库(该数据库需要提前创建)中。主要有两种方法:

1.登出的状态下

mysql -u root -h localhost -p db_test_back < D:\Progra~2\MySQL\mysql-5.7.22-winx64\dbs_back\db_test_back.sql

2.在登入状态下

source D:\Progra~2\MySQL\mysql-5.7.22-winx64\dbs_back\db_test_back2.sql

9.2 数据库的日志

 使用日志首先要先配置,我的日志配置如下(日志配置在[mysqld]标签下):

#log
#错误日志
log-error=D:/Program Files/MySQL/mysql-5.7.22-winx64/logs/err.log
#全查询日志 
log_output=FILE
#不启用的话慢日志查询会存在数据表中
general_log=on
general_log_file=D:/Program Files/MySQL/mysql-5.7.22-winx64/logs/query.log
#慢查询日志
slow_query_log=on
long_query_time =2  
#慢于2秒的会被记录
slow_query_log_file=D:/Program Files/MySQL/mysql-5.7.22-winx64/logs/slowquery.log

#二进制日志配置,第二、三行配置最后一个binlog-bin和binlog是不加后缀的文件名,不加后缀的话有妙用
server-id=1
log_bin=D:/Program Files/MySQL/mysql-5.7.22-winx64/logs/log_bin/binlog-bin
log_bin_index=D:/Program Files/MySQL/mysql-5.7.22-winx64/logs/log_bin/binlog
# 过期时间和最大二进制日志大小
expire_logs_days=10
max_binlog_size=100M

配置完重启MySQL服务即可,到对应目录下查看可以看到日志文件,使用SHOW BINARY LOGS;查看二进制文件,使用show binlog events;查看二进制操作记录,Insert,Update语句。二进制日志可以向上面那样配置一个失效时间自动删除,也可以手动删除二进制日志,使用RESET MASTER;(所有二进制日志将被删除,mysql 会重新创建二进制日志,新的日志文件扩展名将重新从000001开始编号)。也可以使用PURGE MASTER LOGS TO "joe-bin.000003";删除指定的某个日志文件;此外,还可以删除指定时间之前的所有日志文件,语法为PURGE MASTER LOGS BEFORE '20130330'(意为删除2013年3月30日前创建的所有日志文件)。

10. mysql实际开发的经验

 在实际开发中,会有一些值得注意的点,下面简单记录一下。

10.1 关于更新时间

 在设计表时,通常会将创建时间和更新时间带上(即create_timeupdate_time),在更新记录时往往在代码层面不断的去手动设置new Date(),而且由于这个字段的存在感可能不是很强,所以有时候常常会忘了在更新时同时去更新这个字段,一旦后面需要用到的时候发现这个字段从来没有更新过,这就很尴尬,其实mysql可能帮我们做这个事,不需要我们自己去做,只要在设计表时指定update_time datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP即可,下面是一个完整的表结构:

CREATE TABLE school (
	id INT ( 16 ) NOT NULL Auto_increment COMMENT "id",
	name VARCHAR ( 32 ) NOT NULL COMMENT "学校名",
	-- 创建时间直接默认给当前时间
	create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT "创建时间",
	-- 更新时间默认给当前时间,且在更新记录时自动更新为当前时间
	update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "更新时间",
PRIMARY KEY ( id ) 
) ENGINE = INNODB DEFAULT charset = utf8;

10.2 关于IP

 mysql中有专门处理ip的函数,不要直接用字符型存放IP地址,一是无法占用空间较大(满位是255.255.255.255占用16字节),而将其转成int型只需要4字节;二是无法对IP进行筛选;三对于字符类型检索也很慢。若将IP地址格式定义为A.B.C.D,那将其转成INT数值类型就为A*2^24+B*2^16+C*2^8+D,最大值为4294967295,所以int类型4位32字节足够了,主要涉及2个函数INET_ATON([ipStr])(将字符串IP转成整数型)和INET_NTOA(ipInt)(将整型数值转成字符型IP地址)。

10.3 联合查询增加条件

 联合查询条件是指在join X on condition1后面通过and关键字继续追加条件(如and a.name like "test%"或者and b.category_id=20等等),会造成什么结果,这个问题其实对于大部分的开发极少用到,一般而言就是on A.b_id=b.id,就拿left join为例,过程如下:将所有的结果都取出来,主表全显、次表只显示满足联合联合条件的,总体而言联合条件不会影响记录的总条数,只是影响次表是否展示。这里区别一下和where条件后面使用and追加条件就好了,where条件称之为全局条件,它是会影响最终的结果数目的,示例如下:

-- 语句1,left join 在on后面又跟了条件 r.name like '%门%'
SELECT sc.id,sc.name,sc.shop_id,r.id r_id, r.name from shop_component sc
LEFT JOIN rfa r ON r.id = sc.component_id and r.name like '%门%'

-- 语句2,直接使用 where 进行过滤
SELECT sc.id,sc.name,sc.shop_id,r.id r_id, r.name from shop_component sc
LEFT JOIN rfa r ON r.id = sc.component_id 
WHERE r.name like '%门%'

-- 语句3,left join 在on后面又跟了条件 sc.name like '%门%'(主表)
SELECT sc.id,sc.name,sc.shop_id,r.id r_id, r.name from shop_component sc
LEFT JOIN rfa r ON r.id = sc.component_id and sc.name like '%门%'

对照上面的结论:
语句1:在普通join的结果上加了限制,所以 shop_component 主表全显,关联表 rfa 只显示满足 name like '%门%' 条件的记录,其他记录的关联表都为空;
语句2:直接在最后加了where条件,它是对整个结果集进行了过滤,只在中间结果集中取满足 name like '%门%' 条件的记录,其他记录不限制,所以最终的结果记录要比语句1更少;
语句3:只是 left join x and 后面的条件有关联表rfa的name like '%门%'改成了主表 shop_compoment 的name like '%门%',结果是类似于语句1的,这时主表也是全显,关联表只显示满足sc.name like '%门%'条件的记录;

综上所述,left join on x and y这种语句的on语句并不会影响结果记录数,主表是全显的,关联表只会显示满足and之后条件的记录,其他为null。

10.4 小表驱动大表

 这算是sql优化的一种方式吧,它的使用场景主要有两种:存在于和join操作。

 存在于判断主要是inexists操作:

-- 如果B表小、A表大,应优先使用 in 关键字,先循环的是  select id from B,这样利用这个小表来驱动A这个大表
select A.* from A where A.b_id in (select id from B)

-- 如果A表小、B表大,应优先使用 exists 关键字,这样先循环的就是 select A.* from A(小表),然后在小表内再去验证是否满足 select 1 from B where B.id=A.b_id(大表)
select A.* from A where exists (select 1 from B where B.id=A.b_id)

其实 inexists 主要是看场景、看他们的驱动表,MySQL 表关联的算法是 Nest Loop Join,是通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。

 第二种就是联合查询了,左连接就尽量让左表尽量小,右连接就尽量让右表尽量小。

# 如左连接查询应该尽量保证左表尽可能的小
select A.* from A left join B on A.b_id = B.id

10.5 mysql中的锁

 现有主流mysql开发基本都是使用的INNODB引擎,INNODB引擎支持表锁和行锁(myISAM仅支持表锁),细分为 record lock、gap lock 和 next-key lock(即record lock + gap lock)。比如在同时修改同一条记录时就会出现其中一个线程(会话)在一直等待,或者select * from A for update(直接加写锁,仅允许一个线程加写锁)和select * from A lock in share mode(读锁,多个线程可以同时加读锁,和写锁互斥,一旦加了读锁就无法再加写锁),像alter tableinsert into A select * from B(锁表A)这类操作表的行为会锁表。若需要验证上述的内容,需要在2个会话里面进行操作,并且将自动提交事务关闭(或者使用begin ... commit)。若出现慢SQL的话,排查的时候可能需要考虑到这种情况,主要可以这样排查:

-- 检查mysql的进程情况
show processlist
-- 查看正在被锁定的表
show OPEN TABLES where In_use > 0;
-- 查看事务锁的情况
select * from INFORMATION_SCHEMA.INNODB_TRX;

-- 杀掉指定的进程,pid就是 processlist 中的id
kill [pid]

 有些时候我们在mysql中会来做乐观锁和悲观锁的方法,具体方案如下:

  • 乐观锁:我们直接在表中新增一个version字段,每次更新前我们都比较一下当前记录的version,如果version字段和我们手中的version字段一致,那就进行更新同时将version字段进行更新(可以是自增或者时间戳),否则不更新;
  • 悲观锁:在查询后添加for update即可人工加锁,即所谓的悲观锁的实现方案;

10.6 关于分布式锁

 现在分布式锁的主流实现方式是ZK(最小文件节点)和redis(setNX),其实mysql本身也可以用来做分布式锁的解决方案,mysql实现分布式锁主要是靠唯一键的约束,和redis的实现方式比较类似,直接以约定好的key(可以是指定好的字符串,可以是日期+方法名)插入DB中对应的列,该列做唯一约束,那么当多个线程同时操作时只可能有一个线程可以插入成功,其他的插入失败,其中插入成功的那个线程就表示成功获取了锁,其他插入失败的就表示没有抢到锁。

10.7 数据库黑名单

 即某台主机或者ECS被mysql拉入黑名单,无法连接,通常发生的场景多发生在网络较差的情况,服务端和DB Server多次短时间的连接、断开,反复多次会出现这样的情况,在java服务端抛出的异常大概如下:

Caused by: java.sql.SQLException: null, message from server: "Host '10.129.60.199' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"

同一个ip在短时间内产生太多(超过mysql数据库max_connection_errors的最大值)中断的数据库连接而导致的阻塞;

解决方式:mysqladmin flush-hosts,可以直接在客户端运行flush-hosts

10.7 代码层面实现mysql的手动事务提交

 在一些数据库双写的场景下可能需要手动提交事务,单独使用mybatis可以这样关闭事务的自动提交
SqlSession sqlSession = sqlSessionFactory.openSession(false),在与SpringBoot集成后,可以作如下实现:

    @Autowired
    private DataSourceTransactionManager transactionManager;
    @Autowired
    TransactionDefinition transactionDefinition;
	@Autowired
	private UserMapper userMappper;
	@Autowired
	private UserSearchClient userSearchClient;
	
	TransactionStatus transaction = transactionManager.getTransaction(transactionDefinition);
	
	public void delete(Long id) {
		try {
			// mysql的行为
			userMapper.delete(id);
			// es的行为
			userSearchClient.delete(id);
			transactionManager.commit(transaction);
		} catch(Exception e) {
			logger.error("user delete occur error.", e);
			transactionManager.rollback(transaction);
		}
	}

最后推一款mysql的分析和优化工具:percona-toolkit,可以用来做扫描重复索引之类的工作。

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页