MySQL积累

MySQL积累基本基础安装账号编码变量类型MyISAMHeap基础字段类型操作索引索引方法B-Tree索引哈希索引空间数据索引(R-Tree)全文索引创建和删除索引和KEY索引和引擎引擎CSVMEMORYInnoDBMyISAMInnoDB和MyISAM的区别FEDERATED视图高级锁类型表锁行锁页锁死锁解决存储过程创建调用函数库函数字符串处理类型转换IP处理url处理日期处理自定义函数事件开启事件事件语法触发器创建触发器删除触发器查询索引索引类型正则正则模糊匹配子查询分类关键字exists关键字any关键字all关键字连接joininner joinleft joinright joinunion(all)cross joinfull joinexists和in积累基础语句类型运行方式技巧插入技巧自增列信息筛选restful接口sandman2xmysql选取结果添加行号近段时间数据修复选取指定日期字符包含问题跨库Join问题应用行转列列转行同一属性多值过滤关联更新周同期字符分割的数组长度不存在则插入,存在则更新删除重复数据GroupTopN调优配置优化具体方法索引慢查询配置分析读写分离分库分表备份复制导入和导出导入导出同步mysql之间同步mysql导入到redis恢复binlog性能性能指标性能监控explain命令extended-status命令show global statuspercona-toolkit工具箱MySQL Utilities Client工具箱工具一览具体使用:问题面试自增主键参考

基本

基础

安装

先检查已安装版本

 
AخA
1
 # 查看已安装
2
 rpm -qa | grep mysql
3
 # 卸载
4
 rpm -e --nodeps mysql_xxxx

包管理器安装

 
x
1
#1.下载mysql的repo源
2
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
3
# 或者新版:http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
4
5
#2.安装mysql-community-release-el7-5.noarch.rpm包
6
sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
7
#安装这个包后,会获得两个mysql的yum repo源:
8
vim /etc/yum.repos.d/mysql-community.repo
9
vim /etc/yum.repos.d/mysql-community-source.repo
10
11
#3.安装mysql
12
sudo yum install mysql-server

配置开机启动

 
xxxxxxxxxx
4
1
cp ./support-files/mysql.server /etc/init.d/mysqld
2
chmod +x /etc/init.d/mysqld
3
chkconfig --add mysqld
4
chkconfig mysqld on
账号

创建新用户并设置密码

 
xxxxxxxxxx
1
1
create user 'username'@'host' identified by 'password';

授权管理

 
xxxxxxxxxx
13
1
grant privileges on databasename.tablename to 'username'@'host';
2
# 其中的privileges可以是SELECT , INSERT , UPDATE或者all等 
3
# 查看所有用户的授权
4
select * from information_schema.user_privileges;
5
6
# 查看所有的用户
7
select distinct concat('user: ''',user,'''@''',host,''';') as query from mysql.user;
8
9
# 查看某用户的授权
10
show grants for 'root'@'%';
11
12
# 查看某用户的所有信息
13
select * from mysql.user where user='cactiuser' \G;   

更改密码

 
xxxxxxxxxx
17
1
# 命令:
2
set password for 'username'@'host' = password('newpassword');
3
# 如果是当前登陆用户用
4
 set password = password("newpassword");
5
# 例子: set password for 'lin'@'%' = password("123456");
6
7
# 或者
8
update user set password=password('123456') where user='root';
9
10
# 授权的同时修改密码
11
GRANT ALL PRIVILEGES ON `db1`.* TO 'root'@'%' IDENTIFIED by '123';
12
13
# 授权给已有的用户
14
GRANT ALL PRIVILEGES ON `snh48`.* TO 'root'@'%';
15
16
# 使用mysqladmin修改密码
17
mysqladmin -u root -p password 'root' # 如果原始密码是空,直接回车即可

远程登陆

 
xxxxxxxxxx
2
1
mysql -uroot -pxxx -P3316 -h127.0.0.1 -Ddb1
2
# 其中-P3316是本机的转发端口(如果不转发的话,直接是远程机器的端口),注意在cmder中输入命令,不要在GitBash中输入,后者正确之后无响应
编码

查看字符数据库的字符集

 
xxxxxxxxxx
4
1
show variables like 'character\_set\_%';
2
show variables like 'collation_%';
3
4
# 或者直接使用status命令查看

设置数据库字符编码

 
xxxxxxxxxx
21
1
create database mydb character set utf8;    # 创建的时候指定字符编码
2
3
# 以下修改是永久性的修改
4
alter database mydb character set utf8;     # 修改数据库编码(若之前的设置不是utf8编码的话)
5
alter table tbl character set utf8;         # 修改表的编码
6
alter table tbl modify col_name varchar(50) CHARACTER SET utf8; # 修改字段的编码
7
8
# 以下的修改是基于窗口的修改,窗口关闭则失效
9
set names 'utf8';                   # 等同于执行client,connection,results三个的设置
10
set character_set_client=utf8;      # 客户端编码方式
11
set character_set_connection=utf8;  # 建立连接使用的编码
12
set character_set_database=utf8;    # 数据库的编码
13
set character_set_results=utf8;     # 结果集的编码
14
set character_set_server=utf8;      # 数据库服务器的编码
15
set character_set_system=utf8;
16
17
set character_set_filesystem=binary; 
18
19
set collation_connection=utf8;
20
set collation_database=utf8;
21
set collation_server=utf8;

转换路径

信息输入路径:client→connection→server;
信息输出路径:server→connection→results。

换句话说,每个路径要经过3次改变字符集编码。以出现乱码的输出为例,server里utf8的数据,传入connection转为latin1,传入results转为latin1,utf-8页面又把results转过来。如果两种字符集不兼容,比如latin1和utf8,转化过程就为不可逆的,破坏性的。所以就转不回来了。

解决办法

# CLIENT SECTION
[mysql]
default-character-set=utf8

# SERVER SECTION
[mysqld]
default-character-set=utf8
# init_connect='SET NAMES utf8' # 设定连接mysql数据库时使用utf8编码,以让mysql数据库为utf8运行
# character-set-server=utf8 	# 其它类似的设置都可以在这里指定(有问题,设置不了),# The default 									character set that will be used when a new schema or table is
								created and no character set is defined

以上两个设置会导致以下的字符集设置为utf8:

而以下的的设置还是默认值(当换成[client]目录下的修改,则生效):

然后重启mysql服务service mysql restart,或者/etc/init.d/mysql restart

命令行链接的时候指定编码:

mysql --default-character-set=utf8  -uroot -proot -Dpgv_stat_yingyin

汇总:

需要保证的是,以下4个的编码方式一致:

可通过以下方式达到:

tips:

 
xxxxxxxxxx
2
1
# python连接mysql的时候指定编码或者socket
2
 conn = MySQLdb.connect(host="localhost", user="xxxx", passwd="xxx",use_unicode=True, charset="utf8",unix_socket='/tmp/mysql_3309.sock')
变量

类型转换

 
xxxxxxxxxx
3
1
# 字符串转整型
2
SELECT CONVERT(filedName, UNSIGNED INTEGER);
3
SELECT CAST(filedName as SIGNED);

查看变量

 
xxxxxxxxxx
4
1
# 命令行:mysqladmin variables -p,这个操作也就相当于登录时使用命令 show global variables;
2
3
# 若查看某种类型的变量
4
show [global] variables like "%_time";

修改变量

 
xxxxxxxxxx
6
1
# 临时修改
2
set global long_query_time=2;
3
4
# 永久修改
5
在my.cnf的[mysqld]节点下添加
6
var_name=var_value

例子:

 
xxxxxxxxxx
2
1
# mysql表名大小写的敏感性配置:
2
show variables like '%lower_case_table_names%';

类型

共有5种类型的表格:

MyISAM

//待补充

Heap

内存表的特点:

 
xxxxxxxxxx
5
1
- 内存表不支持事务和AUTO_INCREMENT
2
- 内存表不支持BLOB/TEXT列
3
- 内存表大小可用 max_heap_table_size 参数来设置
4
- 只能使用比较运算符=,<,>,=>,= <
5
- 索引不能为NULL

内存表使用小结

基础

创建表

 
xxxxxxxxxx
8
1
CREATE TABLE if not exists `row2col_tbl` (
2
  `date` varchar(8) DEFAULT NULL,
3
  `xl_version` varchar(20) DEFAULT NULL,
4
  `stat_flag` varchar(10) DEFAULT NULL,
5
  `area` varchar(2) DEFAULT NULL,
6
  `cnt` int(11) DEFAULT NULL,
7
  `cnt_user` int(11) DEFAULT NULL
8
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

修改表名

 
xxxxxxxxxx
3
1
alter table media_relation_search_pc rename media_relation_search_pc_old_20170627;
2
# 或者
3
rename命令格式:rename table 原表名 to 新表名;

查看包含某字段的所有表名和所在的数据库

 
xxxxxxxxxx
3
1
#TABLE_SCHEMA字段为db的名称(所属的数据库),字段TABLE_NAME为表的名称。
2
SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.columns WHERE column_name='brand_id';
3
show tables like "xxx%";

查看一个表的所有字段

 
xxxxxxxxxx
2
1
# shell的方式(得到的结果是一串由空格分割的字符串,然后再进行遍历即可)
2
fields=$(echo "desc media_info.${TABLE_NAME};"| ${LOCAL_MYSQL} | grep -v Field | grep -v auto_increment | awk '{print $1}')
字段
类型

json

json类型是在mysql5.7及之后的版本中添加的

 
xxxxxxxxxx
1
1
操作

增加字段

 
xxxxxxxxxx
2
1
alter table table_name add field_name field_type;
2
alter table newexample add address varchar(110) after stu_id;

修改字段

 
xxxxxxxxxx
1
1
alter table table_name change old_field_name new_field_name field_type;

删除字段

 
xxxxxxxxxx
1
1
alter table table_name drop field_name;

更新字段

 
xxxxxxxxxx
7
1
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
2
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
3
    [WHERE where_condition]
4
    [ORDER BY ...]
5
    [LIMIT row_count]
6
7
UPDATE tbl SET col1 = col1 + 1, col2 = col1;

索引

索引认知:

  1. 索引是按照特定的数据结构把数据表中的数据放在索引文件中,以便于快速查找;
  1. 索引存在于磁盘中,会占据物理空间。
索引方法

索引文件按照不同的数据结构来存储,数据结构的不同产生了不同的索引类型,常见的索引类型

B-Tree索引

支持范围查找

哈希索引

哈希表的优势与限制:

空间数据索引(R-Tree)

地理数据存储

全文索引

全文索引主要用于海量数据的搜索,只有InnoDB存储引擎支持

总结

1. B-Tree索引使用最广泛,主流引擎都支持。
2. 哈希索引性能高,适用于特殊场合。
3. R-Tree不常用。
4. 全文索引适用于海量数据的关键字模糊搜索。

创建和删除

加索引

mysql> alter table 表名 add index 索引名 (字段名1[,字段名2 …]);

加主关键字的索引

mysql> alter table 表名 add primary key (字段名);

例子: mysql> alter table employee add primary key(id);

加唯一限制条件的索引

mysql> alter table 表名 add unique 索引名 (field1,filed2);

例子: mysql> alter table employee add unique emp_name2(cardnumber);

删除某个索引

mysql> alter table 表名 drop index 索引名;

添加约束

 
xxxxxxxxxx
4
1
# 单列约束
2
ALTER TABLE Persons ADD UNIQUE (Id_P);
3
# 多列约束
4
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName);

撤销约束

 
xxxxxxxxxx
2
1
# unique是索引的一种
2
ALTER TABLE Persons DROP INDEX uc_PersonID;
索引和KEY
 
xxxxxxxxxx
14
1
CREATE TABLE `user_follow` (
2
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
3
  `userID` varchar(16) NOT NULL DEFAULT '',
4
  `starID` varchar(16) NOT NULL DEFAULT '',
5
  `status_e` tinyint(4) NOT NULL DEFAULT '0',
6
  `follow_t` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
7
  `insert_t` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
8
  `update_t` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
9
  `ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
10
  PRIMARY KEY (`id`),
11
  UNIQUE KEY `follow` (`userID`,`starID`),
12
  KEY `userID` (`userID`),
13
  KEY `starID` (`starID`)
14
) ENGINE=MyISAM AUTO_INCREMENT=13912 DEFAULT CHARSET=utf8;

注意PRIMARY KEY,UNIQUE KEY, KEY的区别

索引和约束

UNIQUE KEYPRIMARY KEY约束均为列或列集合提供了唯一性的保证,PRIMARY KEY拥有自动定义的 UNIQUE 约束,一个表可以有多个UNIQUE KEY约束,但是只能有一个PRIMARY KEY约束。

 
xxxxxxxxxx
17
1
CREATE TABLE Persons(
2
    Id_P int NOT NULL,
3
    LastName varchar(255) NOT NULL,
4
    FirstName varchar(255),
5
    Address varchar(255),
6
    City varchar(255),
7
    UNIQUE (Id_P)
8
);
9
10
CREATE TABLE Persons(
11
    Id_P int NOT NULL,
12
    LastName varchar(255) NOT NULL,
13
    FirstName varchar(255),
14
    Address varchar(255),
15
    City varchar(255),
16
    CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
17
)
索引和引擎

引擎

存储引擎

 
xxxxxxxxxx
10
1
# 修改表的存储引擎
2
alter table table_name engine=innodb;
3
4
# 查找使用InnoDB引擎的表
5
select table_schema,table_name from information_schema.tables where table_type='base table' and engine='innodb' and table_schema!='mysql' and table_name not like '%innodb%';
6
7
# 关闭InnoDB的存储引擎(修改默认的存储引擎)
8
#修改my.ini文件:
9
到default-storage-engine=INNODB 改为default-storage-engine=MyISAM
10
找到#skip-innodb 改为skip-innodb

MyISAM引擎格式的数据可以被文件复制,然后恢复,而InnoDB引擎不可以同步文件使用。

CSV
CSV(Comma-Separated Values逗号分隔值)
   使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件(所以,它可以将CSV类型的文件当做表进行处理),这种文件是一种普通文本文件,每个数据行占用一个文本行。该种类型的存储引擎不支持索引,即使用该种类型的表没有主键列;另外也不允许表中的字段为null。
MEMORY

也称为HEAP

    该存储引擎通过在内存中创建临时表来存储数据。每个基于该存储引擎的表实际对应一个磁盘文件,该文件的文件名和表名是相同的,类型为.frm。该磁盘文件只存储表的结构,而其数据存储在内存中,所以使用该种引擎的表拥有极高的插入、更新和查询效率。这种存储引擎默认使用哈希(HASH)索引,其速度比使用B-+Tree型要快,但也可以使用B树型索引。由于这种存储引擎所存储的数据保存在内存中,所以其保存的数据具有不稳定性,比如如果mysqld进程发生异常、重启或计算机关机等等都会造成这些数据的消失,所以这种存储引擎中的表的生命周期很短,一般只使用一次。
InnoDB

待补充

MyISAM
InnoDB和MyISAM的区别
区别点MyISAMInnoDB
构成上每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。 .frm文件存储表定义。 数据文件的扩展名为.MYD (MYData)。 索引文件的扩展名是.MYI (MYIndex)。基于磁盘的资源是InnoDB表空间数据文件和它的日志文件,InnoDB 表的大小只受限于操作系统文件的大小,一般为 2GB
事务处理上MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持InnoDB提供事务支持事务,外部键等高级数据库功能
SELECT ,UPDATE,INSERT,Delete操作如果执行大量的SELECT,MyISAM是更好的选择1.如果你的数据执行大量的INSERT**或UPDATE,出于性能方面的考虑,应该使用InnoDB表
2.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
3.LOAD TABLE FROM MASTER**操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用
对AUTO_INCREMENT的操作每表一个AUTO_INCREMEN列的内部处理。 MyISAM**为INSERTUPDATE操作自动更新这一列**。这使得AUTO_INCREMENT列更快(至少10%)。在序列顶的值被删除之后就不能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列,可以出现重使用从序列顶部删除的值的情况)。 AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置 对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引 更好和更快的auto_increment处理如果你为一个表指定AUTO_INCREMENT列,在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器,它被用在为该列赋新值。 自动增长计数器仅被存储在主内存中,而不是存在磁盘上 关于该计算器的算法实现,请参考 AUTO_INCREMENT**列在InnoDB里如何工作**
表的具体行数select count() from table,MyISAM只要简单的读出保存好的行数,注意的是,当count()语句包含 where条件时,两种表的操作是一样的InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行
表锁提供行锁(locking on row level),提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs),另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”
FEDERATED

主要用于将不同的数据库服务器上的数据组合起来

 
xxxxxxxxxx
2
1
# 开启引擎支持
2
在windows下只需要在mysql的配置文件 my.ini 最末尾加上一句federated 

参考:MySQL中的各种引擎的区别

视图

为什么要用视图?视图的优点如下:

  1. 安全性
  2. 提高查询性能
  3. 灵活应对功能的改变
  4. 复杂的查询需求

通过更新视图更新真实表,更新视图的方法:

高级

引擎和锁类型的对应关系

表类型(支持的)锁类型死锁备注
MyISAM表锁不存在
InnoDB行锁/表锁存在默认是行锁
BDB页锁/表锁存在

加锁

 
xxxxxxxxxx
1
1
锁类型
表锁

表锁有两种模式:

MyISAM表的读和写是串行的,即在读操作时不能写操作,写操作时不能读操作。

行锁

//待添加

页锁

//待添加

死锁解决

//待添加

存储过程

存储过程和函数的区别:

创建
 
xxxxxxxxxx
27
1
CREATE PROCEDURE sp_name([IN|OUT|INOUT] param_name type) [characteristics] routine_body
2
3
#1.其中CREATE PROCEDURE为创建存储过程的关键字,sp_name为存储过程的名称,为指定存储过程的参数
4
#2.characteristics指定存储过程的特性
5
#3.routine_body是SQL代码的内容, 可以用BEGIG...END来表示SQL代码的开始和结束
6
7
#例子:
8
DROP PROCEDURE IF EXISTS test_add;
9
DELIMITER //
10
CREATE PROCEDURE test_add()
11
BEGIN
12
  DECLARE 1_id INT DEFAULT 1;
13
  DECLARE 1_id2 INT DEFAULT 0;
14
  DECLARE error_status INT DEFAULT 0;
15
  DECLARE datas CURSOR  FOR SELECT id FROM test;  # 声明游标
16
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET error_status=1;
17
  OPEN datas;  # 打开游标
18
    FETCH datas INTO 1_id;
19
    REPEAT
20
      SET  1_id2=1_id2+2;
21
      UPDATE test SET id2=1_id2 WHERE id=1_id;
22
      FETCH datas INTO 1_id;
23
      UNTIL  error_status
24
    END REPEAT;
25
  CLOSE  datas;
26
END
27
//
调用
 
xxxxxxxxxx
2
1
call  sp_name;
2
# 存储过程的调用一般是在事件中进行

函数

库函数
字符串处理

日期字符串转换

 
xxxxxxxxxx
32
1
# 单字符串
2
## mysql法
3
concat_ws('/',substring(date,1,4),substring(date,5,2),substring(date,7,2))
4
5
## awk法
6
echo "20161212"| awk '{print substr($1,1,4)"/"substr($1,5,2)"/"substr($1,7,8)}'
7
echo "20161212"| awk '{printf("%s/%s/%s",substr($1,1,4),substr($1,5,2),substr($1,7,8))}'
8
9
## shell法(注意shell循环读入变量的方式)
10
a=20161212
11
while read line;do echo ${line:0:4}"/"${line:4:2}"/"${line:6:2}; done<<< "${a}"
12
13
# 文件
14
## mysql法
15
#导入导出麻烦
16
17
## awk法(指定列修改)
18
awk '{for(i=1;i<NF;i++) if(i==1) printf("%s/%s/%s",substr($i,1,4),substr($i,5,2),substr($i,7,8));else printf("\t%s",$i);printf("\n");}' user_pos_num
19
20
##shell法
21
fin='xxx.data'
22
while read line;do
23
    linearr=($line)
24
    for i in `seq 0 $((${#linearr[@]}-1))`;do
25
        if [ $i -eq 0 ];then
26
            echo -n ${line:0:4}"/"${line:4:2}"/"${line:6:2}
27
        else
28
            echo -n -e  "\t"${linearr[$i]}
29
        fi
30
    done
31
    echo 
32
done< "$fin"

备注: 日期从20161212转换成2016/12/12,后者的格式能容易被excel处理

trim函数

 
xxxxxxxxxx
6
1
#trim函数可以过滤指定的字符串: 
2
#完整格式:TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) 
3
#简化格式:TRIM([remstr FROM] str) 
4
# 默认是BOTH和删除两端的空格
5
6
SELECT TRIM(TRAILING ',' FROM ',bar,xxyz,'); 
类型转换
 
xxxxxxxxxx
4
1
# 字符串转数字
2
SELECT CAST('123' AS SIGNED);
3
SELECT CONVERT('123',SIGNED);
4
SELECT '123'+0;
IP处理
 
xxxxxxxxxx
9
1
# int->ip
2
select inet_ntoa(3507806248); #209.20.224.40 
3
4
# 还存在问题
5
select concat_ws('.',cast(3507806248/pow(256,3) as signed),cast((3507806248%pow(256,3))/pow(256,2) as signed),cast(3507806248/pow(256,1) as signed),cast(3507806248/pow(256,0) as signed));
6
7
# ip->int
8
select 209*pow(256,3)+20*pow(256,2)+224*pow(256,1)+40*pow(256,0); # 3507806248
9
select inet_aton('209.20.224.40'); # 3507806248
url处理
 
xxxxxxxxxx
2
1
# 提取url域名
2
select substring_index(substring_index('http://wz.cnblogs.com/my/search/?q=cookie','/',3),'/',-1);
日期处理
 
xxxxxxxxxx
1
1
# 待补充
自定义函数
 
xxxxxxxxxx
8
1
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2
  BEGIN
3
    set N=N-1;
4
  RETURN (
5
      # Write your MySQL query statement below.
6
      SELECT IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N,1), NULL)  
7
    );
8
END
 
xxxxxxxxxx
13
1
CREATE FUNCTION factorial(n INT) RETURNS INT
2
BEGIN
3
    -- 阶乘
4
    DECLARE i INT DEFAULT 1;
5
    DECLARE result INT DEFAULT 0;
6
    
7
    WHILE i<=n DO
8
        SET result=result*i;
9
        SET i=i+1;
10
    END WHILE;
11
    
12
    RETURN result;
13
END

事件

类似于linux的crontab的事件调度器(event-scheduler),可定期执行某一个命令或者sql语句,通常的应用场景是通过事件来调用存储过程。

查看事件是否开启:

SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
SHOW PROCESSLIST;

如果看到event_scheduler为on或者PROCESSLIST中显示有event_scheduler的信息说明就已经开启了事件。如果显示为off或者在PROCESSLIST中查看不到event_scheduler的信息,那么就说明事件没有开启,我们需要开启它。

开启事件
SET GLOBAL event_scheduler = ON;
更改完这个参数就立刻生效了

注意:还是要在my.cnf中添加event_scheduler=ON。因为如果没有添加的话,mysql重启事件又会回到原来的状态了。

更改配置文件然后重启

在my.cnf中的[mysqld]部分添加如下内容,然后重启mysql。
event_scheduler=ON

通过制定事件参数启动
mysqld ... --event_scheduler=ON
事件语法

创建

 
xxxxxxxxxx
20
1
CREATE
2
    [DEFINER = { user | CURRENT_USER }]
3
    EVENT
4
    [IF NOT EXISTS]
5
    event_name
6
    ON SCHEDULE schedule
7
    [ON COMPLETION [NOT] PRESERVE]
8
    [ENABLE | DISABLE | DISABLE ON SLAVE]
9
    [COMMENT 'comment']
10
    DO event_body;
11
12
schedule:
13
    AT timestamp [+ INTERVAL interval] ...
14
     | EVERY interval
15
    [STARTS timestamp [+ INTERVAL interval] ...]
16
    [ENDS timestamp [+ INTERVAL interval] ...]
17
18
interval:
19
  quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |WEEK | SECOND | YEAR_MONTH | DAY_HOUR |
20
            DAY_MINUTE |DAY_SECOND | HOUR_MINUTE |HOUR_SECOND | MINUTE_SECOND}

参数详细说明:

DEFINER: 定义事件执行的时候检查权限的用户。

ON SCHEDULE schedule: 定义执行的时间和时间间隔。

ON COMPLETION [NOT] PRESERVE: 定义事件是一次执行还是永久执行,默认为一次执行,即NOT PRESERVE。

ENABLE | DISABLE | DISABLE ON SLAVE: 定义事件创建以后是开启还是关闭,以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话,会自动加上DISABLE ON SLAVE。

COMMENT 'comment': 定义事件的注释。

更改

 
xxxxxxxxxx
1
1
alter ...

删除

 
xxxxxxxxxx
1
1
DROP EVENT [IF EXISTS] event_name

触发器

mysq表中允许有以下六种触发器:

创建触发器
 
xxxxxxxxxx
9
1
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
2
###参数说明:
3
# trigger_time是触发程序的动作时间。它可以是 before 或 after,以指明触发程序是在激活它的语句之前或之后触发。
4
# trigger_event指明了激活触发程序的语句的类型
5
    INSERT:将新行插入表时激活触发程序
6
    UPDATE:更改某一行时激活触发程序
7
    DELETE:从表中删除某一行时激活触发程序
8
#tbl_name:监听的表,必须是永久性的表,不能将触发程序与TEMPORARY表或视图关联起来。
9
#trigger_stmt:当触发程序激活时执行的语句。执行多个语句,可使用BEGIN...END复合语句结构
删除触发器
 
xxxxxxxxxx
5
1
DROP TRIGGER [schema_name.]trigger_name
2
#可以使用old和new代替旧的和新的数据
3
#更新操作,更新前是old,更新后是new.
4
#删除操作,只有old.
5
#增加操作,只有new.

一个例子:

 
xxxxxxxxxx
3
1
create TRIGGER up_conter after insert on documents for each row BEGIN
2
set new.content=new.title; #注意不要在触发器的操作语句中使用update等操作
3
END

参考:

MySQL触发器简单实例

already used by statement

MySQL触发器

查询

基础查询

 
xxxxxxxxxx
2
1
# 按汉字的首字母拼音排序
2
SELECT name_varchar from study.datatype order by CONVERT(name_varchar USING gbk) ;

索引

单索引和联合索引

索引类型

索引类型是基于索引方法的,有各自的特点

区别在于:

主键索引:数据列不允许重复,不允许为NULL.一个表只能有一个主键。
唯一索引:数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引。
普通索引:基本的索引类型,没有唯一性的限制,允许为NULL值。

正则

mysql正则和模糊匹配的区别:

正则
 
xxxxxxxxxx
2
1
# 正则判断(匹配返回1,不匹配返回0)
2
select 'JetPack 1000'  regexp '^1000';

注:

模糊匹配

子查询

子查询是从最内层的查询开始执行的

分类

根据子查询的返回值分:

根据子查询的出现位置分:

关键字
exists关键字

内层查询语句不返回查询记录,而是返回一个真假值。

 
xxxxxxxxxx
4
1
select employee_name,gender,email,job_title from employee where exists(select * from employee where employee_name='成龙');
2
3
# 返回a表中满足id在b表中条件的记录
4
select * from ecs_goods a where EXISTS(select cat_id from ecs_category b where a.cat_id = b.cat_id);
any关键字

只要满足内存查询语句返回结果中的任意一个,就可以执行外层查询语句

 
xxxxxxxxxx
2
1
#这个就是查询所有购买数量大于49的订单的信息!
2
select order_id,customer_id,order_number,order_date from `order` where order_id = any(select order_id from order_detail where buy_number>49);
all关键字
 
xxxxxxxxxx
2
1
#所有满足订单的总金额大于单价*10的订单的信息
2
select order_id,customer_id,order_number,order_date from `order` where total_money > all(select price*10 from order_detail);

连接

join
inner join
 
xxxxxxxxxx
16
1
#SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
2
#FROM Persons
3
#INNER JOIN Orders
4
#ON Persons.Id_P = Orders.Id_P
5
#ORDER BY Persons.LastName;
6
7
# 该语句等效于
8
SELECT
9
    Persons.LastName,
10
    Persons.FirstName,
11
    Orders.OrderNo
12
FROM
13
    Persons,
14
    Orders
15
WHERE
16
    Persons.Id_P = Orders.Id_P
left join
 
xxxxxxxxxx
10
1
SELECT
2
    Persons.LastName,
3
    Persons.FirstName,
4
    Orders.OrderNo
5
FROM
6
    Persons
7
LEFT JOIN Orders ON Persons.Id_P = Orders.Id_P
8
WHERE persons.LastName like 'C%'
9
ORDER BY
10
    Persons.LastName 
right join
 
xxxxxxxxxx
9
1
SELECT
2
    Persons.LastName,
3
    Persons.FirstName,
4
    Orders.OrderNo
5
FROM
6
    Persons
7
RIGHT JOIN Orders ON Persons.Id_P = Orders.Id_P
8
ORDER BY
9
    Persons.LastName
union(all)
 
xxxxxxxxxx
5
1
# 请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
2
# 注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
3
SELECT  * from  persons
4
UNION All
5
SELECT  * from  persons;
cross join
 
xxxxxxxxxx
13
1
SELECT
2
    b.*
3
from
4
    student a,
5
    persons b
6
where
7
    a.stuno = b.Id_P;
8
9
# 等同于
10
#SELECT b.* from student a CROSS JOIN persons b where a.stuno=b.Id_P;
11
# 总结:
12
#1,带where子句的cross join 和inner join(或者join)等效
13
#2,不带where子句的产生的查询结果才是笛卡尔积

连接tips:

full join

mysql本身不支持full join 需要变通解决

 
xxxxxxxxxx
1
1
# 本身

exists和in
 
xxxxxxxxxx
5
1
#这条语句适用于a表比b表大的情况
2
select * from ecs_goods a where cat_id in(select cat_id from ecs_category);
3
4
#这条语句适用于b表比a表大的情况
5
select * from ecs_goods a where EXISTS(select cat_id from ecs_category b where a.cat_id = b.cat_id);

积累

基础

语句类型

DML、DDL、DCL区别 .

 
xxxxxxxxxx
8
1
DML(data manipulation language):
2
它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
3
4
DDL(data definition language):
5
DDL比DML要多,主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用
6
7
DCL(Data Control Language):
8
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。在默认状态下,只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL
运行方式技巧
 
xxxxxxxxxx
15
1
# 方法1
2
${MYSQL10} < xmp_version_active.sql
3
#其中MYSQL10是:`/usr/bin/mysql -uroot -phive -N`
4
5
# 方法2
6
MYSQL="/usr/bin/mysql -uxxxx -pxxxx -hxxxx -Pxxxx"
7
sql="select movieid,pageurl,posterurl from poster_to_down where image_type='poster' and ts >='${time_start}'"
8
echo "${sql}" | ${MYSQL} media_info |sed '1d' > ${file}
9
10
Local_MYSQL="/usr/bin/mysql -uxxxxx -pxxxx -hxxxxx media_info"
11
echo "alter table media_relation_search_pc rename media_relation_search_pc_old_$(date +'%Y%m%d');" | $Local_MYSQL
12
13
# 方法3
14
cat /data/rsync_data/kk_sql/videos.sql |$mysql video
15
echo "show tables;"| mysql -uroot -proot -Dsnh48

一次运行多个sql文件

 
xxxxxxxxxx
5
1
#在文件 batch.sql 中写下多个SQL文件
2
source file1.SQL
3
source file2.SQL
4
source file3.SQL
5
#然后运行 source batch.sql
插入技巧
 
xxxxxxxxxx
5
1
# 一次性插入多个值
2
INSERT into task_request(proposer,enddate) values ("鲁丽",'20170611'),("张一",'20170322'),("王二",'20170101');
3
4
# 从tb1中选出两列插入到tb2中
5
INSERT into tb2(proposer,enddate) select xx,yy from tb1;
自增列
 
xxxxxxxxxx
7
1
# 建表的时候指定
2
# > // id列为无符号整型,该列值不可以为空,并不可以重复,而且id列从100开始自增.
3
create table table_1 ( id int unsigned not null primary key auto_increment, 
4
                       name varchar(5) not null ) auto_increment = 100;
5
6
# 修改自增列的值
7
alter table table_1 auto_increment = 2;

只能修改单机的,集群修改自增列无效

信息筛选

查询某个字段匹配的的表和所在的数据库

 
xxxxxxxxxx
1
1
SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from information_schema.`COLUMNS` where COLUMN_NAME like '%isover%';

筛选某种类型的表和所在的库

 
xxxxxxxxxx
1
1
select table_schema,table_name from information_schema.tables where table_type='base table' and engine='innodb' and table_schema!='mysql' and table_name not like '%innodb%';

restful接口

sandman2
 
xxxxxxxxxx
3
1
pip install sandman2
2
sandman2ctl 'mysql+mysqldb://root:root@localhost/pgv_stat_yingyin'
3
* Running on http://0.0.0.0:5000/

其中mysql的链接方式可以有以下几种

 
xxxxxxxxxx
11
1
# default
2
engine = create_engine('mysql://scott:tiger@localhost/foo')
3
4
# mysql-python
5
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
6
7
# MySQL-connector-python
8
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')
9
10
# OurSQL
11
engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')

sandman2ctl的配置有以下:

optional arguments:
  -h, --help            show this help message and exit
  -d, --debug           Turn on debug logging
  -p PORT, --port PORT  Port for service to listen on
  -l, --local-only      Only provide service on localhost (will not be
                        accessible from other machines)
  -r, --read-only       Make all database resources read-only (i.e. only the
                        HTTP GET method is supported)
  -s SCHEMA, --schema SCHEMA
                        Use this named schema instead of default

问题是中文的查询结果是unicode显示,命令行配置jq才能正常显示,而web访问还没有查到显示中文的方式

xmysql

为mysql数据库快速生成restful api

选取结果添加行号

 
xxxxxxxxxx
9
1
# 方法1 
2
set @mycnt=0;
3
select (@mycnt := @mycnt + 1) as ROWNUM , vv from task1_tbl order by vv;
4
5
# 方法2
6
# #将查询结果写入到某个拥有auto_increment字段的临时表中再做查询
7
8
# 方法3
9
# #用Python等脚本语言对查询结果进行二次组装

近段时间数据修复

 
xxxxxxxxxx
26
1
# 用上周同期的数据浮动修复本周的数据
2
delete from db2.tb1 where date>=20160818 and date<=20160821;
3
insert into db2.tb1 select
4
    date_format(date_add(date, interval 7 day),'%Y%m%d'),
5
    channel,
6
    version,
7
    install_begin + round(install_begin / 100),
8
    install_end + round(install_end / 100),
9
    uninstall+round(uninstall/100)
10
from
11
    db2.tb1
12
where
13
    date >= '20160811' and date <= '20160814';
14
15
# 用除了修复日期外其它近段时间的数据平均修复
16
delete from db2.tb1 where date='20170313';
17
insert into db2.tb1 select
18
    '20170313',
19
    channel,
20
    version,
21
    avg(install_begin),
22
    avg(install_end),
23
from
24
    db2.tb1
25
where
26
    date='20170309'  and  date<='20170315' and date!='20170313';

选取指定日期

 
xxxxxxxxxx
1
1
select (DATEDIFF(DATE_ADD(curdate, INTERVAL - DAY(curdate)+ 1 DAY), date_add(curdate- DAY(curdate)+ 1, INTERVAL -1 MONTH)))  as '上月总天数', DATE_ADD(curdate,interval -day(curdate)+1 day) as '当月第一天', date_add(curdate-day(curdate)+1,interval -1 month ) as '上月第一天';

这段还没有完全调通

字符包含问题

判断某个字段是否包含某个字符串的方法

 
xxxxxxxxxx
9
1
# 方法1:
2
SELECT * FROM users WHERE emails like "%b@email.com%";
3
4
# 方法2:find_in_set(subtr,str)函数是返回str中substr所在的位置索引,str必须以","分割开,若没有返回0。
5
SELECT find_in_set('3','13,33,36,39') as test;
6
7
# 方法3:locate(substr,str)函数,如果包含,返回>0的数,否则返回0 
8
# 例子:判断site表中的url是否包含'http://'子串,如果不包含则拼接在url字符串开头
9
update site set url =concat('http://',url) where locate('http://',url)=0;

例子:

 
xxxxxxxxxx
1
1
select if(b.channel_type is NULL,'其它',b.channel_type),a.channel from channels_data a LEFT JOIN channels_conf b on FIND_IN_SET(a.channel,b.channels)>0;

跨库Join问题

链接表的使用要求FEDERATED 的打开,默认是关闭的

 
xxxxxxxxxx
11
1
# 链接表的创建
2
CREATE TABLE `link_tbl` (
3
`uninstalldate`  varchar(10)  NOT NULL DEFAULT '' ,
4
`newinstalldate`  varchar(10)  NOT NULL DEFAULT '' ,
5
`coverinstalldate`  varchar(10)  NOT NULL DEFAULT '' 
6
)
7
ENGINE=FEDERATED
8
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
9
ROW_FORMAT=COMPACT
10
CONNECTION='mysql://root:root@localhost:3306/task/xmp_uninstall'  
11
COMMENT='task.xmp_uninstall-链接表[3306]';

链接表的注意事项:

1.本地的表结构必须与远程的完全一样

2.远程数据库目前仅限MySQL(其它主流数据库暂不支持)

3.不支持事务

4.不支持表结构修改

应用

行转列

有两种实现方式case wheninner join:

 
xxxxxxxxxx
19
1
# 原始的的数据格式是按行的,现在想拼接为列,其中还有汇总和所占比,实现方式如下:
2
select a.date,a.f1 as '总量',b.f2 as '360域名总量',
3
round(c.f3*100/b.f2,2) as 'http://hao.360.cn/?src=lm&ls=n4abc0a4199',
4
round(d.f4*100/b.f2,2) as 'http://hao.360.cn/?src=lm&ls=n79f40a409c',
5
round(e.f5*100/b.f2,2) as 'http://hao.360.cn/?src=lm&ls=n110c004e9b',
6
round(f.f6*100/b.f2,2) as 'https://hao.360.cn/?src=lm&ls=n556c014b9f'
7
from
8
(select date,sum(cnts) as f1 from pgv_stat.xmpcloud2_ie_stat where date>=20161201 group by date) a
9
inner join
10
(select date,sum(cnts) as f2 from pgv_stat.xmpcloud2_ie_stat where date>=20161201 and host_url='360.cn' group by date) b on a.date=b.date
11
inner join
12
(select date,cnts as f3 from pgv_stat.xmpcloud2_ie_stat  where  date>=20161201 and url='http://hao.360.cn/?src=lm&ls=n4abc0a4199') c on a.date=c.date
13
inner join
14
(select date,cnts as f4 from pgv_stat.xmpcloud2_ie_stat  where  date>=20161201 and url='http://hao.360.cn/?src=lm&ls=n79f40a409c') d on a.date=d.date
15
inner join
16
(select date,cnts as f5 from pgv_stat.xmpcloud2_ie_stat  where  date>=20161201 and url='http://hao.360.cn/?src=lm&ls=n110c004e9b') e on a.date=e.date
17
inner join
18
(select date,cnts as f6 from pgv_stat.xmpcloud2_ie_stat  where  date>=20161201 and url='https://hao.360.cn/?src=lm&ls=n556c014b9f') f on a.date=f.date
19
order by a.date desc;

案例参考:

季度销售量
1991111
1991212
1991313
1991414
1992121
1992222
1992323
1992424
一季度二季度三季度四季度
199111121314
199221222324
 
xxxxxxxxxx
21
1
# 实现的sql语句(借鉴意义很广泛)
2
select 年, 
3
    sum(case when 季度=1 then 销售量 else 0 end) as 一季度, 
4
    sum(case when 季度=2 then 销售量 else 0 end) as 二季度, 
5
    sum(case when 季度=3 then 销售量 else 0 end) as 三季度, 
6
    sum(case when 季度=4 then 销售量 else 0 end) as 四季度 
7
from sales group by 年;
8
9
select 年, 
10
    max(case when 季度=1 then 销售量 else 0 end) as 一季度, 
11
    max(case when 季度=2 then 销售量 else 0 end) as 二季度, 
12
    max(case when 季度=3 then 销售量 else 0 end) as 三季度, 
13
    max(case when 季度=4 then 销售量 else 0 end) as 四季度 
14
from sales group by 年;
15
16
select 年, 
17
    max(if(季度=1,销售量,0) as 一季度, 
18
    max(if(季度=2,销售量,0) as 二季度, 
19
    max(if(季度=3,销售量,0) as 三季度, 
20
    max(if(季度=4,销售量,0) as 四季度 
21
from sales group by 年;

在sql server 2005中有pivot函数可以实现同样的功能

列转行

有两种实现方式:序列化表union all

 
xxxxxxxxxx
20
1
# 利用序列化表的方式实现列转行
2
SELECT
3
    user_name,
4
    REPLACE (SUBSTRING(SUBSTRING_INDEX(mobile, ',', a.id), CHAR_LENGTH(SUBSTRING_INDEX(mobile, ',', a.id - 1)) + 1), ',', '') as moblie
5
FROM
6
    seq_tb a
7
CROSS JOIN (
8
    SELECT
9
        CONCAT(mobile, ',') as mobile,
10
        LENGTH(mobile) - LENGTH(REPLACE(mobile, ',', '')) + 1 size
11
    FROM
12
        user1 b
13
) b on a.id <= b.size;
14
15
# 利用union实现列转行
16
SELECT user_name,'skills1' as 'jineng',skills1 from nameskills_col
17
UNION ALL
18
SELECT user_name,'skills2',skills2 from nameskills_col
19
UNION ALL
20
SELECT user_name,'skills2',skills3 from nameskills_col ORDER BY user_name;

这部分还有很多要完善的地方!,进一步扩展的是求一行的最大值和最小值等

同一属性多值过滤

选出同时具有fei和bianhua能力的人

 
xxxxxxxxxx
16
1
# 方法一
2
SELECT DISTINCT a.name AS 'feibianren' from nameskills a 
3
JOIN  nameskills b on a.name=b.name and b.skills='fei'
4
join  nameskills c on a.name=c.`name` and c.skills='bianhua';
5
6
# 方法二:
7
SELECT
8
    a.`name`,
9
    b.skills as bskill
10
    #c.skills as cskill
11
from
12
    nameskills_row a
13
INNER JOIN join nameskills_row b on a.name = b.name
14
and b.skills = 'nianjing';
15
#join nameskills_row c on a.name = c.name
16
#and c.skills = 'fanren';

关联更新

根据另一个表的数据,更新当前表的数据:

 
xxxxxxxxxx
2
1
# 在a表和b表满足xx条件的时候更新a表的什么内容
2
update pgv_stat.xmp_version_active a inner join (select date,version,sum(online_user) user,sum(total_uv) vod from pgv_stat.xmp_total_vod where date='$dt' and channel='all' group by version) b on a.date=b.date and substring_index(a.version,'.',-1)=b.version set a.online_user=b.user,a.total_uv=b.vod where a.date='$dt';
 
xxxxxxxxxx
5
1
# 在a表和b表满足xx条件的时候更新a表的什么内容
2
UPDATE downloaddatas a, downloadfee b SET a.ThunderPrice=$PRICE, a.ThunderAMT=(a.ThunderCop+a.btdownnum3)*$PRICE WHERE a.CopartnerId=b.copartnerid AND b.inuse=1 AND a.BalanceDate>=DATE_FORMAT(b.starttime,'%Y-%m-%d') AND a.BalanceDate='$BALANCEDATE'
3
4
#在a表和b表满足xx条件的时候更新b表的什么内容
5
update union_kuaichuan_download_data a,downloaddatas b set b.ThunderQty=b.ThunderQty+a.copdowntimes where a.dayno=$d and b.BalanceDate=_gbk\"${dt}\" and b.CopartnerId=a.copid  and b.ProductNo=4"

周同期

 
xxxxxxxxxx
11
1
# 这周的数据
2
tablea="select date,sum(install_end) as s_install_end,sum(install_silence) as s_install_new from xmp_install where date>=date_sub(curdate(),interval 7 day) group by date"
3
4
# 上周的数据
5
tableb="select date,sum(install_end) as s_install_end,sum(install_silence) as s_install_new from xmp_install where date>=date_sub(curdate(),interval 14 day) group by date"
6
7
# 要统计的数据
8
whatis="a.date as '当前日期',b.date as '上周同期',a.s_install_end as '总安装量',b.s_install_end as '上周同期总安装量',concat(round((a.s_install_end-b.s_install_end)*100/b.s_install_end,2),'%') as '总装周同比'"
9
10
# 展示结果
11
sql = "SELECT {whatis} FROM ({tablea}) a INNER JOIN ({tableb}) b on b.date=DATE_FORMAT(DATE_SUB(a.date,INTERVAL 7 day),'%Y%m%d') order by a.date desc".format(whatis=whatis,tablea=tablea,tableb=tableb)

字符分割的数组长度

 
xxxxxxxxxx
2
1
# imgName格式:bc9077f6.jpg,073eb23f.jpg
2
select if(imgName='',0,1+(length(imgName)-length(replace(imgName,',','')))) as arraycnt from contribute;

不存在则插入,存在则更新

 
xxxxxxxxxx
6
1
INSERT INTO tablename (field1, field2, field3, ...) VALUES ('value1', 'value2','value3', ...) ON DUPLICATE KEY UPDATE field1='value1', field2='value2', field3='value3', ...
2
3
# 这个语句的意思是,插入值,如果没有该记录执行
4
INSERT INTO tablename (field1, field2, field3, ...) VALUES ('value1', 'value2','value3', ...)
5
# 这一段,如果存在该记录,那么执行
6
UPDATE field1='value1', field2='value2', field3='value3', ...

一个例子:

 
xxxxxxxxxx
1
1
INSERT INTO tablea (peerid,new_install_date,new_install_source,new_install_version,new_install_type,insert_date,insert_source,insert_version,insert_type) VALUES("%s","%s","%s","%s","%s","%s","%s","%s","%s") ON DUPLICATE KEY UPDATE new_install_type="%s"' 

删除重复数据

在删除的时候可能会根据某些条件保留其中的一条

 
xxxxxxxxxx
6
1
# 删除重复邮件地址,重复的多条,只保留其中id最小的
2
delete from Person 
3
where Id not in (select a.Id from (
4
                                    select min(Id) as Id from Person group by Email
5
                                    )a
6
                );

GroupTopN

问题描述:先分组,然后在从分组中选取前N个值,比如topN

 
xxxxxxxxxx
36
1
# 例子1:(遍历所有记录,取每条记录与当前记录做比较,只有当同一版本不超过3个比当前高时,这个才是前三名)。
2
SELECT
3
    *
4
FROM
5
    study.row2col_tbl AS e
6
WHERE
7
    (
8
        SELECT
9
            count(DISTINCT(e1.cnt))
10
        FROM
11
            study.row2col_tbl AS e1
12
        WHERE
13
            e1.date = e.date and e1.xl_version=e.xl_version
14
        AND e1.cnt > e.cnt) < 2
15
 ORDER BY e.date;
16
17
# 例子2:连接其它表
18
SELECT
19
    b.department_name AS Department,
20
    a.name AS Employee,
21
    a.salary AS Salary
22
FROM
23
    study.employes AS a
24
INNER JOIN study.department AS b ON a.department_id = b.id
25
WHERE
26
    (
27
        SELECT
28
            count(DISTINCT(a1.salary))
29
        FROM
30
            study.employes AS a1
31
        WHERE
32
            a1.department_id = a.department_id
33
        AND a1.salary > a.salary
34
    ) < 3
35
ORDER BY
36
    b.department_name DESC,a.salary DESC;

用awk,python,R,Shell等如何实现

调优

问题排查部分请单独查看性能部分

配置优化

//待补充

具体方法

索引

索引通过减少查询必须扫描的数据库中的数据量来提高查询效率,如何设计索引的使用,索引会引入额外的性能问题,比如插入会稍慢。

慢查询

慢查询日志将日志记录写入文件,也将日志记录写入数据库表。

配置
 
xxxxxxxxxx
20
1
# 查询慢日志是否开启及文件存储位置
2
show variables  like '%slow_query_log%';
3
4
# 查询慢日志查询时间
5
show variables like 'long_query_time%';
6
# 注意:使用命令 set global long_query_time=4修改后,需要重新连接或新开一个会话才能看到修改值。你用show variables like 'long_query_time'查看是当前会话的变量值,你也可以不用重新连接会话,而是用show global variables like 'long_query_time'; 
7
8
# 输出存储格式
9
show variables like '%log_output%';
10
# MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'
11
12
# 未使用索引的查询是否被记录到慢查询日志中
13
show variables like 'log_queries_not_using_indexes';
14
15
# 系统变量log_slow_admin_statements表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志
16
show variables like 'log_slow_admin_statements';
17
18
19
# 如果你想查询有多少条慢查询记录,可以使用系统变量。  
20
show global status like '%Slow_queries%';
分析

mysqldumpslow分析工具提供对慢日志查询的分析

-s, 是表示按照何种方式排序,
  c: 访问计数
  l: 锁定时间
  r: 返回记录
  t: 查询时间
  al:平均锁定时间
  ar:平均返回记录数
  at:平均查询时间
-t, 是top n的意思,即为返回前面多少条的数据;
-g, 后边可以写一个正则匹配模式,大小写不敏感的;
 
xxxxxxxxxx
12
1
2
# 得到返回记录集最多的10个SQL。
3
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
4
5
# 得到访问次数最多的10个SQL
6
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
7
8
# 得到按照时间排序的前10条里面含有左连接的查询语句。
9
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
10
11
# 另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现刷屏的情况。
12
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
读写分离

一写多读,写入的数据实时从写库同步到读库,开源代理插件360的atlas

分库

分布式和事务的控制

分表

用法和拆分原则要一开始的时候设计好

备份

备份对比

备份方法备份速度恢复速度便捷性功能应用场景备注
cp一般、灵活性低很弱少量数据备份
mysqldump一般、可无视存储引擎的差异一般中小型数据量的备份
lvm2快照一般、支持几乎热备、速度快一般中小型数据量的备份
xtrabackup较快较快实现innodb热备、对存储引擎有要求强大较大规模的备份

复制

只复制表结构

 
xxxxxxxxxx
1
1
create table xx_bak like xxx;

复制表结构和数据(不复制索引)

 
xxxxxxxxxx
2
1
create table if not exists xx_bak select * from xxx;
2
# 该语句只复制数据,不复制索引和key

若要完整的复制表,使用下面的方式:

 
xxxxxxxxxx
2
1
create table 复制表 like 表;
2
insert into 复制表 select * from

导入和导出

导入

load data方法 :将数据文件加载进mysql: 官方参考

 
xxxxxxxxxx
5
1
# 指定编码,分割符之类的,如果不指定编码,容易出现乱码
2
mysql -uroot -proot -e "delete from db1.tb1;load data local infile './members.csv' into table db1.tb1 character set utf8  fields terminated by ',' LINES TERMINATED BY '\n';"
3
4
# 指定导入到哪几列(这几列不一定要连续)
5
sql="load data local infile '$datapath/db1.odl_put_context_${date}' into table  odl_put_context(Fdb,Ftbl,Fdate,Fhour,Fput_status);"

运行插入语句

 
xxxxxxxxxx
6
1
# 首先将数据导出成可运行的sql语句,然后
2
> source xxx.sql
3
# 或者
4
mysql -uxxxx -pxxx < xxx.sql
5
mysql -uxxxx -pxxx --default-character-set=utf8 < xxx.sql
6
### 注意使用这种方式的时候,在xxx.sql里最好指定编码,例如使用set names utf8
导出
 
xxxxxxxxxx
14
1
#1.导出整个数据库 
2
#mysqldump -u用户名 -p密码  数据库名 > 导出的文件名 
3
mysqldump -uroot -pmysql db1   > e:\db1.sql 
4
5
#2.导出一个表,包括表结构和数据 
6
#mysqldump -u用户名 -p密码  数据库名 表名> 导出的文件名 
7
mysqldump -uroot -pmysql db1 tb1 tb2> e:\tb1_tb2.sql 
8
9
#3.导出一个数据库结构 
10
mysqldump -uroot -pmysql -d db1 > e:\db1.sql 
11
12
#4.导出一个表,只有表结构 
13
#mysqldump -u用户名 -p 密码 -d数据库名  表名> 导出的文件名 
14
mysqldump -uroot -pmysql -d db1 tb1> e:\tb1.sql 

注意:

可以给mysqldump添加以下参数,来设置输出格式:

fields terminated by '字符串':设置字符串为字段之间的分隔符,可以为单个或多个字符。默认值是“\t”。 fields enclosed by '字符':设置字符来括住字段的值,只能为单个字符。默认情况下不使用任何符号。 fields optionally enclosed by '字符':设置字符来括住CHAR、VARCHAR和TEXT等字符型字段。默认情况下不使用任何符号。 fields escaped by '字符':设置转义字符,只能为单个字符。默认值为“\”。 lines starting by '字符串':设置每行数据开头的字符,可以为单个或多个字符。默认情况下不使用任何字符。 lines terminated by '字符串':设置每行数据结尾的字符,可以为单个或多个字符。默认值是“\n”。

导出中文乱码的解决方式:

--default-character-set=gb2312

mysqldump导出优化

 
xxxxxxxxxx
4
1
show variables like '%max_allowed_packet%';
2
show variables like '%net_buffer_length%';
3
4
mysqldump --defaults-extra-file=/etc/my.cnf  test -e --max_allowed_packet=1048576  --net_buffer_length=16384 > test.sql

 
xxxxxxxxxx
10
1
# 方法1:mysql语句
2
> select * from db1.tb1 limit 2 into outfile '/tmp/xxx.xls'  fields terminated by ',' ;
3
# 注意mysql用户是否具有写的权限,另外可配置是否显示列名
4
5
# 方法2:重定向(查询自动写入文件,查询结果不再显示在窗口
6
> pager cat >> /tmp/test.xls;
7
8
# 方法3:输出重定向
9
mysql -h 127.0.0.1 -u root -p XXXX -P3306 -e "select * from table"  > /tmp/test.xls
10
# 若不想显示列名,加—N参数即可

tips:导出某些表的表结构

 
xxxxxxxxxx
5
1
tbls=($(echo "show tables;"| mysql -uroot -proot -Dsnh48))
2
for tbl in ${tbls[@]:1};do
3
    #mysqldump -uroot -proot -d study $tbl> ${tbl}.sql 
4
    echo "mysqldump -uroot -proot -d snh48 $tbl> ${tbl}.sql "
5
done

同步

mysql之间同步
- 不同机器之间的数据库只能利用导入和导出法
- 不同数据库之间利用导入和导出的方法
	- 也可以利用外连接表的方法
- 同数据库不同表之间可以利用insert select的方法
该工具可完成以上所有情况下的任务,但是需要手动操作,不能自动化
logstash的更强大的功能待挖掘
通过编程利用mysql的api操作完成
对MyISAM引擎存储的文件,可以使用文件同步的方法
mysql导入到redis
利用各自的接口api实现
 
xxxxxxxxxx
12
1
# 方法1:创建shell脚本mysql2redis_mission.sh,(在mysql的结果中进行命令行的组合)内容如下:
2
mysql db_name --skip-column-names --raw < mission.sql | redis-cli [--pipe]
3
# 进化方法
4
mysql -uroot -proot -N <redis_pipe.sql |redis-cli
5
6
# 例如
7
(echo "set key1 vale1\r\n get key1\r\n") |redis-cli [--pipe]#最后这个pipe选项可能导致问题
8
# 或者(能得到返回结果)
9
(echo -en "set key3 vale3\r\n get key2\r\n") |nc localhost 6379
10
11
# 方法2:直接将文件内容输入到流
12
cat xxx.file |redis-cli [--pipe]
 
xxxxxxxxxx
1
1
输入源是mysql,输出源是redis,可以利用中间的filter达到初步的处理

恢复

binlog

查看binlog状态

 
xxxxxxxxxx
1
1
show variables like '%log_bin%';  

开启binlog

 
xxxxxxxxxx
10
1
log_bin=ON  
2
log_bin_basename=/var/lib/mysql/mysql-bin  
3
log_bin_index=/var/lib/mysql/mysql-bin.index  
4
# 或者简便方式
5
log-bin=/var/lib/mysql/mysql-bin
6
7
# 说明
8
# 第一个参数是打开binlog日志
9
# 第二个参数是binlog日志的基本文件名,后面会追加标识来表示每一个文件
10
# 第三个参数指定的是binlog文件的索引文件,这个文件管理了所有的binlog文件的目录

性能

性能指标

性能指标计算和监控,指标关注如下:

指标定义备注
tps/qps每秒事务数/查询数
线程状态
流量状态
innodb读写
innodb日志
innode行
myisam读写
myisam缓冲池
临时表
响应时间
其它

性能监控

explain命令

核心优先使用explain一下查问题,对explain基础知识的理解

 
xxxxxxxxxx
1
1
explain format=json select avg(k) from sbtest1 where id between 1000 and 2000 \G

注意查询开销query_cost

extended-status命令
 
xxxxxxxxxx
7
1
# 累计值
2
mysqladmin -uroot -proot extended-status
3
4
# 当前状态(指定刷新频率)
5
mysqladmin -uroot -proot extended-status --relative -i1
6
#或者
7
mysqladmin -uroot -proot extended-status --relative --seleep=1
show global status
 
xxxxxxxxxx
1
1
>show global status;

percona-toolkit工具箱

//该工具箱作为重点学习的对象

MySQL Utilities Client工具箱

工具一览
 
xxxxxxxxxx
31
1
mysqluc> help utilities
2
Utility            Description
3
-----------------  --------------------------------------------------------
4
mysqlauditadmin    audit log maintenance utility
5
mysqlauditgrep     audit log search utility
6
mysqlbinlogmove    binary log relocate utility
7
mysqlbinlogpurge   purges unnecessary binary log files
8
mysqlbinlogrotate  rotates the active binary log file
9
mysqldbcompare     compare databases for consistency
10
mysqldbcopy        copy databases from one server to another
11
mysqldbexport      export metadata and data from databases
12
mysqldbimport      import metadata and data from files
13
mysqldiff          compare object definitions among objects where the
14
                   difference is how db1.obj1 differs from db2.obj2
15
mysqldiskusage     show disk usage for databases
16
mysqlfailover      automatic replication health monitoring and failover
17
mysqlfrm           show CREATE TABLE from .frm files
18
mysqlgrants        display grants per object
19
mysqlindexcheck    check for duplicate or redundant indexes
20
mysqlmetagrep      search metadata
21
mysqlprocgrep      search process information
22
mysqlreplicate     establish replication with a master
23
mysqlrpladmin      administration utility for MySQL replication
24
mysqlrplcheck      check replication
25
mysqlrplms         establish multi-source replication
26
mysqlrplshow       show slaves attached to a master
27
mysqlrplsync       replication synchronization checker utility
28
mysqlserverclone   start another instance of a running server
29
mysqlserverinfo    show server information
30
mysqlslavetrx      skip transactions on slaves
31
mysqluserclone     clone a MySQL user account to one or more new users
具体使用:
 
xxxxxxxxxx
5
1
 # 显示文件使用
2
 mysqldiskusage --server=root:root@localhost study
3
 
4
 # 数据库复制
5
 mysqldbcopy --source=root:root@localhost --destination=xxx:xxx@xxx study:study

问题

面试

自增主键

问题描述:

​ 一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 ?

解决方法:

自增主键

参考