mysql高级功能收集

创建用户和授权

新用户增删改

1.创建用户:
# 指定ip：192.118.1.1的alex用户登录
create user '用户名'@'192.118.1.1' identified by '123';
# 指定ip：192.118.1.开头的alex用户登录
create user '用户名'@'192.118.1.%' identified by '123';
# 指定任何ip的alex用户登录
create user '用户名'@'%' identified by '123';

2.删除用户
drop user '用户名'@'IP地址';


3.修改用户
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

4.修改密码
set password for '用户名'@'IP地址'=Password('新密码');

对用户授权

#查看权限
show grants for '用户'@'IP地址'

#授权 alex用户仅对db1.t1文件有查询、插入和更新的操作
grant select ,insert,update on db1.t1 to "alex"@'%';

# 表示有所有的权限，除了grant这个命令，这个命令是root才有的。alex用户对db1下的t1文件有任意操作
grant all privileges  on db1.t1 to "alex"@'%';
# alex用户对db1数据库中的文件执行任何操作
grant all privileges  on db1.* to "alex"@'%';
# alex用户对所有数据库中文件有任何操作
grant all privileges  on *.*  to "alex"@'%';
 
#取消权限
 
# 取消alex用户对db1的t1文件的任意操作
revoke all on db1.t1 from 'alex'@"%";  

# 取消来自远程服务器的alex用户对数据库db1的所有表的所有权限

revoke all on db1.* from 'alex'@"%";  

取消来自远程服务器的alex用户所有数据库的所有的表的权限
revoke all privileges on *.* from 'alex'@'%';

索引

索引分类

1.普通索引

• 最基本的索引，不具备唯一性，就是加快查询速度
• 创建普通索引

方法一：创建表时添加索引
// 可以使用key，也可以使用index
create table 表名（
	列定义
	index 索引名称 （字段）
	index 索引名称 （字段）
）
create table demo1( id int(4), name varchar(20), pwd varchar(20), key(pwd) );
create table demo2( id int(4), name varchar(20), pwd varchar(20), key index_pwd(pwd) ); 

方法二： 当表创建完成后，使用alter为表添加索引：
alter table 表名 add index 索引名称 （字段1，字段2.....）;

desc table;    Key 对应 MUL
Key是MUL, 就是一般性索引，该列的值可以重复, 
该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL。
就是表示是一个普通索引

删除索引
alter table demo drop key pwd;
alter table demo add key(pwd);

2、唯一性索引

• 与普通索引基本相同，但有一个区别：

  • 索引列的所有值都只能出现一次，即必须唯一，用来约束内容，字段值只能出现一次。应该加唯一索引。
  • 唯一性允许有NULL值<允许为空>

• 创建唯一索引

1.创建表时加唯一索引
create table 表名(
	列定义：
	unique key 索引名 （字段）;
)
常用在值不能重复的字段上，比如说用户名，电话号码，身份证号。

create table demo(id int(4) auto_increment primary key, uName varchar(20),  unique  index  (uName));
Key 字段上有 UNI

2.修改表时加唯一索引
alter table 表名 add unique 索引名 (字段);
alter table demo3 drop key uName;
alter table demo3 add unique(uName);

3、主键索引（主索引）

• 说明

  • 每个表只能有一个主键列，可以有多个普通索引列。
  • 主键列要求列的所有内容必须唯一，而索引列不要求内容必须唯一，
  • 不允许为空

• 创建主键索引

1.创建表创建主键索引
create table demo(
	id int(4) not null auto_increment, 
	name varchar(20) default null,
	primary key(id)
 );

• 主键索引，唯一性索引区别：主键索引不能有NULL，唯一性索引可以有空值

4、复合索引

• 索引可以包含一个、两个或更多个列
• 例如

创建一个表存放服务器允许或拒绝的IP和port，要记录中IP和port要唯一。
create table firewall ( 
	host varchar(15) not null ,
	port smallint(4) not null ,
	access enum('deny','allow') not null, 
	primary key (host,port)   -- 联合主键
); 

5、全文索引 (FULLTEXT INDEX)

• MySQL 5.7.6开始 MySQL内置了ngram全文检索插件，用来支持中文分词
• 全文索引只能用在 varchar text
• 创建全文索引

1. 创建表时创建
create table 表名（
	列定义，
	fulltext key 索引名 （字段）;
）
2.修改表时添加
alter table 表名 add fulltext 索引名 （字段）;
	ALTER TABLE `books` ADD FULLTEXT  [索引名] (`author` ) 

• MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表，如果是其他数据引擎，则全文索引不会生效
• 参考 http://sphinxsearch.com/

常见函数

算坐标的距离

-- ps: mysql version > 5.6
st_distance (POINT (lon,lat),POINT(120.1111,32.2211)) / 0.0111
-- 示例
SELECT
   s.id,s.name,s.lng,s.lat, 
   (st_distance (point (lng, lat),point(115.067,34.76) ) / 0.0111) as distance
FROM table s
HAVING distance<30
ORDER BY distance

日期格式化成字符串函数

DATE_FORMAT(build_time,"%Y-%m-%d %H:%i:%s")

锁

查询加锁

• Innodb 引擎，普通的查询语句默认使用 MVCC (multiple version concurrency control, 即多版本并发控制) 来实现事务的隔离特性，其又称为无锁读或者快照读，无论查询性能还是并发度都强于传统的加锁读
• 加排他锁

BEGIN

SELECT * from tb WHERE id = 1 for UPDATE
-- 没有提交事物之前,其它事物需要等待这个事物提交,或者等待超时
-- select @@innodb_lock_wait_timeout; -- 默认为50s

COMMIT

触发器

• mysql

CREATE TABLE `test`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NULL DEFAULT '',
  PRIMARY KEY (`id`)
);
drop TABLE if EXISTS test_trigger;
CREATE TABLE `test_trigger`  (
  `id` int NOT NULL AUTO_INCREMENT,
	`item_id` int NOT NULL ,
  `name` varchar(255) NULL DEFAULT '',
  `operation_time` datetime(0) NULL,
  `operation_type` varchar(32) NULL DEFAULT '',
  PRIMARY KEY (`id`)
);
-- 插入触发器
drop trigger if EXISTS insert_test_trigger;
create trigger insert_test_trigger after insert on test for each row 
	insert into test_trigger (item_id,name, operation_time, operation_type)
	values (new.id, new.name,  now(), 'insert');

drop trigger if EXISTS  delete_test_trigger;
-- 删除触发器
create trigger delete_test_trigger after delete on test for each row 
	insert into test_trigger  (item_id,name, operation_time, operation_type)
	values (old.id, old.name,  now(), 'delete');
	
drop trigger if EXISTS  update_test_trigger;
-- 更新触发器
create trigger update_test_trigger after update on test  for each row 
	insert into test_trigger (item_id,name, operation_time, operation_type)
	values (new.id, new.name,  now(), 'update');

其它操作

查看进行中的事物

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx

kill trx_mysql_thread_id

允许分组后再查询

SELECT tmp.* from (
	SELECT round(st_distance(POINT(120.91729143258635,31.90614637985159),POINT(aa.lon,aa.lat) ) / 0.011195*1000,2) 
				as tree_distance,
				aa.* 
				FROM stockinfos aa  
			HAVING tree_distance <60 and tree_distance <>0 
			ORDER BY tree_distance  limit 100000
) tmp  GROUP BY tmp.spatialObjectID


SELECT @@sql_mode 
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';


## 永久设置 在mysql的配置文件中加上
sql_mode = 'STRICT_TRANS_TABLES...'

substring_index 将1,2,3 … 替换成 1 2 3

SELECT * FROM sys_menu WHERE id in (
	SELECT substring_index(substring_index(t.mids,',', b.help_topic_id + 1), ',', -1) 
	FROM 
		(SELECT menu_ids mids  from sys_menu_role   WHERE role_id = 1 	) t -- 1,2,3,4,5,6,7
	join mysql.help_topic b ON b.help_topic_id <  (LENGTH(t.mids) - LENGTH(REPLACE(t.mids, ',', '')) + 1)
)

单表自更新 select + update

UPDATE  chinese_laws aaa INNER JOIN
(
SELECT id,CONCAT(chapter,'--',title,'--',number,'--',content) all_content from chinese_laws
) as bbb 
SET aaa.all_content = bbb.all_content
WHERE aaa.id=bbb.id

迁移数据 insert into tableA select from tableB

aa

使用 insert into tableA select * from tableB 语句时，一定要确保 tableB 后面的 where，order 或者其他条件，都需要有对应的索引，来避免出现 tableB 全部记录被锁定的情况
INSERT INTO order_record SELECT * FROM order_today FORCE INDEX (idx_pay_suc_time) WHERE pay_success_time <= '2020-03-08 00:00:00';

按月份统计填补空的月份为0

-- 创建辅助表
CREATE TABLE tb_num (i INT);
INSERT INTO tb_num (i) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

-- 统计sql
SELECT left(temp.date,7) d_year_month,RIGHT(left(temp.date,7),2) d_month,coalesce(u.unmber,0) d_number from
(
        SELECT adddate('2019-01-01', interval numlist.id month) AS 'date' FROM 
                (
                        SELECT * from 
                                (
                                        SELECT n1.i + n10.i * 10 AS id FROM tb_num n1 CROSS JOIN tb_num AS n10
                                ) a
                        where a.id<=11
                ) AS numlist
        WHERE adddate('2019-01-01', interval numlist.id month) <= '2019-12-01'
) temp
LEFT JOIN 

(
        SELECT left(report_time,7) udate,count(report_time) unmber FROM tb_report_result_emergency 
        WHERE (YEAR(report_time) BETWEEN 2018 and 2020)  and line_id = 1
        GROUP BY report_time
) u 
on left(temp.date,7) = u.udate ORDER BY temp.date

查看mysql的连接数量

show processlist
show variables like '%max_connections%'
set global max_connections = 1000

字符串截取

substring

1. substring(str,pos)
-- str 原字符串 , pos 起始位置 1开始 , len 向后截取几位
SELECT SUBSTRING('mysql substring()',6) -- substring()

2. SUBSTRING(str,pos,len)

3. SUBSTRING(str FROM pos)
SELECT SUBSTRING('mysql substring()' from 6)  -- substring()

4. SUBSTRING(str FROM pos FOR len)

自定义排序 order by field

• 示例

select * from table_name
ORDER BY field(field1,'a','c','b',''),
field(field2,'北京','上海','',null)

bin_log 解析

• 前置条件

-- 下面三个同时满足才可以订阅bin_log
show variables like 'log_bin'; --  log_bin       | ON 
show variables like 'binlog_format'; --  | binlog_format | ROW
show variables like 'binlog_row_image'; -- | binlog_row_image | FULL

// show  binary logs;  -- mysql-bin.000001	1459433

canal

• 基于java的,需要先启动canal的server端,再通过客户端和server进行连接

go-mysql

• 不需要依赖canal,直接解析binlog

python-mysql-replication

• 不需要依赖canal,直接解析binlog

from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.event import QueryEvent
from pymysqlreplication.row_event import DeleteRowsEvent, UpdateRowsEvent, WriteRowsEvent
import json
import pymysql
import sys


def main():
    mysql_settings = {'host': 'mysql_host',
                      'port': port, 'user': 'root', 'passwd': '123456'}

    stream = BinLogStreamReader(
        connection_settings=mysql_settings,
        server_id=1,
        log_pos=1456854,  # 设置了也没用
        # resume_stream=True, # j加上没啥用 前面的也接收不到
        blocking=True,  # True 监听操作
        only_schemas=['dbname'],
        only_tables=['table1', 'table2'],
        only_events=[WriteRowsEvent, DeleteRowsEvent, UpdateRowsEvent, QueryEvent])

    for binlogevent in stream:
        if isinstance(binlogevent, QueryEvent):
            # 表结构更新
            if binlogevent.query != "BEGIN":
                print(binlogevent.schema.decode('utf8'))
                print(binlogevent.query)
            continue

        # 行更新
        for row in binlogevent.rows:
            event = {"schema": binlogevent.schema, "table": binlogevent.table}

            if isinstance(binlogevent, DeleteRowsEvent):
                event["action"] = "delete"
                event["values"] = dict(row["values"].items())
                event = dict(event.items())
            elif isinstance(binlogevent, UpdateRowsEvent):
                event["action"] = "update"
                event["before_values"] = dict(row["before_values"].items())
                event["after_values"] = dict(row["after_values"].items())
                event = dict(event.items())
            elif isinstance(binlogevent, WriteRowsEvent):
                event["action"] = "insert"
                event["values"] = dict(row["values"].items())
                event = dict(event.items())
            print(event)
            sys.stdout.flush()

    stream.close()


if __name__ == "__main__":
    main()