note
SELECT
CONCAT('ALTER TABLE `',any_value(TABLE_NAME),'` ', 'ADD ',
 IF(any_value(NON_UNIQUE) = 1,
 CASE UPPER(any_value(INDEX_TYPE))
 WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
 WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
 ELSE CONCAT('INDEX `',
  any_value(INDEX_NAME),
  '` USING ',
  any_value(INDEX_TYPE)
 )
END,
IF(UPPER(any_value(INDEX_NAME)) = 'PRIMARY',
 CONCAT('PRIMARY KEY USING ',
 any_value(INDEX_TYPE)
 ),
CONCAT('UNIQUE INDEX `',
 any_value(INDEX_NAME),
 '` USING ',
 any_value(INDEX_TYPE)
)
)
),'(', GROUP_CONCAT(DISTINCT CONCAT('`', any_value(COLUMN_NAME), '`') ORDER BY SEQ_IN_INDEX ASC SEPARATOR ', '), ');') AS 'Show_Add_Indexes'
FROM information_schema.STATISTICS
 where COLUMN_NAME<>'id'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME ASC, INDEX_NAME ASC;

mysql 导入数据太慢，来教你实用干货 原创

导出 或 导入数据，尽可能的使用 MySQL 自带命令工具 ，不要使用一些图形化的工具 （Navicat…）。因为 MySQL 命令行工具至少要比图形化工具快 2 倍 。

命令工具行方式：

导出整个实例 mysqldump -u 用户名 -p 密码 --all-databases > all_database.sql

导出指定库 mysqldump -u 用户名 -p 密码 --databases testdb > testdb.sql

导出指定表 mysqldump -u 用户名 -p 密码 testdb test_tb > test_tb.sql

导入指定 SQL 文件 （指定导入 testdb 库中） mysql -u 用户名 -p 密码 testdb < testdb.sql

小技巧 - 方案二 修改参数方式：

在 MySQL 中，有这么一对参数很有意思，分别是：

“ innodb_flush_log_at_trx_commit ”

与

“ sync_binlog ”

安全性考虑，这个参数默认是 1 ，为了快速导入 sql 数据，可临时修改默认参数值。

参数一: innodb_flush_log_at_trx_commit 默认值为 1，可设置为 0、1、2

innodb_flush_log_at_trx_commit 设置为 0，log buffer 将每秒一次地写入 log file 中，并且 log file 的 flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。

innodb_flush_log_at_trx_commit 设置为 1，每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，并且 flush(刷到磁盘)中去。

innodb_flush_log_at_trx_commit 设置为 2，每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file.但是 flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL 会每秒执行一次 flush(刷到磁盘)操作。

参数二: sync_binlog 默认值为 1，可设置为[0,N)

当 sync_binlog =0，像操作系统刷其他文件的机制一样，MySQL 不会同步到磁盘中去而是依赖操作系统来刷新 binary log。

当 sync_binlog =N (N>0) ，MySQL 在每写 N 次 二进制日志 binary log 时，会使用 fdatasync()函数将它的写二进制日志 binary log 同步到磁盘中去。

注意：这两个参数可以在线修改，若想快速导入，可按照以下命令行

1.进入 MySQL 命令行 临时修改这两个参数 set global innodb_flush_log_at_trx_commit = 2;

set global sync_binlog = 2000;

2.执行 SQL 脚本导入 mysql -uroot -pxxxxxx testdb < testdb.sql

docker exec -i mysql sh -c "exec mysql -uroot -proot -v" < xxx.sql

mysql -uroot -p -v -e "source /xxx.sql";

3.导入完成 再把参数改回来 set global innodb_flush_log_at_trx_commit = 1;

set global sync_binlog = 1;

ALTER TABLE my_table DISABLE KEYS; -- 禁用所有索引 -- 执行批量插入... ALTER TABLE my_table ENABLE KEYS; -- 重新启用索引并重建

分库分表

shardingsphere


由三款开源的分布式数据库中间件 sharding-jdbc、sharding-proxy 和 sharding-sidecar 所构成

分片规则配置

jdbc规范重写

sql解析

sql改写

sql路由

sql执行

结果归并

零填充

CREATE TABLE `user` (
  `id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

索引失效

explain

事务传播


REQUIRED：支持当前事务，如果当前不存在则新开启一个事务（默认配置）
SUPPORTS：支持当前事务，如果当前不存在事务则以非事务方式执行
MANDATORY：支持当前事务，如果当前不存在事务则抛出异常
REQUIRES_NEW：创建一个新事务，如果当前已存在事务则挂起当前事务
NOT_SUPPORTED：以非事务方式执行，如果当前已存在事务则挂起当前事务
NEVER：以非事务方式执行，如果当前已存在事务则抛出异常
NESTED：如果当前存在事务，则在嵌套事务中执行，否则开启一个新事务

replace

sql

UPDATE xxxx SET resid=REPLACE(xx,'1','2'),roleid=replace(xxx,'1','2');

随机

   SELECT
	*  
FROM
	( SELECT ( @ROW := @ROW + 1 ) AS rownum,xxx.* FROM xxx  xxx,( SELECT @ROW := 0 ) AS t ) AS temp 

WITH RECURSIVE sequence AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 FROM sequence WHERE num < 10
)
SELECT * FROM sequence;


select tt.row from
    (
    SELECT cast( concat(t.0,t2.0,t3.0) + 1 As UNSIGNED) as 'row' FROM 
    (select 0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t,
    (select 0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, 
    (select 0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3
    ) tt
    order by tt.row

password

sql

 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'xxxx';


  FLUSH PRIVILEGES;

mybatis 变量

WHERE  xxx.`status` =${@com.test.xxxx@YES.getCode()}

on where 加条件区别

在使用 left join 时，on 和 where 条件的区别如下：

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

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

放 on 里是左连接前过滤，放外边 where 里是对结果进行过滤。先和后的问题

grant all privileges on . to 'root'@'%' identified by '123456' with grant option; mysql 8.0以上

CREATE USER sd@'%' ; -- 创建用户 ALTER USER root@'%' IDENTIFIED BY '123456'; -- 指定密码 GRANT ALL PRIVILEGES ON . TO root@'%' WITH GRANT OPTION; -- 授权

jdbc批处理


rewriteBatched-Statements=true&useServerPrepStmts=false

高阶函数

窗口函数: 对分组统计结果中的每一条记录进行计算

WITH 子句是 MySQL 中的一种 SQL 结构，又称为 Common Table Expression (CTE)

SELECT REGEXP_REPLACE('[Hello World]', '\\[|\\]', '') AS result;

ROW_NUMBER( ) OVER ()

    RANK() OVER()

    PARTITION BY

    order by field()

    lag (last value)
    lead (lead value)

select count 效率

count(*) ≈ count(常量) > count(id) > count(字段)

数据库中表

SELECT
	t.table_name AS table_name,
	GROUP_CONCAT( c.column_name ORDER BY c.ordinal_position ASC ) AS column_name 
FROM
	information_schema.`tables` t
	LEFT JOIN information_schema.`columns` c ON t.table_name = c.table_name 
	AND t.table_schema = c.TABLE_SCHEMA 
WHERE
	t.table_schema = 'xxx' 
GROUP BY
	t.table_name;



SELECT
	table_schema,
	table_name,
	update_time,
	table_rows 
FROM
	information_schema.`TABLES` 
WHERE
	table_type = 'BASE TABLE' 
	AND ENGINE = 'InnoDB' 
ORDER BY
	UPDATE_TIME DESC,
	table_rows DESC 
	LIMIT 10;

树结构的递归


WITH RECURSIVE cte AS (
 
SELECT id, pid, res_name FROM resource WHERE pid IS NULL  
 
UNION ALL
 
SELECT t.id, t.pid, t.res_name FROM resource t JOIN cte c ON t.pid = c.id )
 
SELECT * FROM cte;

select * from (
SELECT t1.id, ifnull(t1.pid,''), t1.res_name FROM resource t1 WHERE t1.pid IS NULL  
union all
SELECT t1.id, ifnull(t1.pid,''), t1.res_name FROM resource t1 JOIN resource t2 ON t1.pid=t2.id) as t order by t.id asc

变量


SET @myVar = 100;
SELECT @myVar;

ognl

ognl Object-Graph Navigation Language 对象导航图语言

mybatis

动态sql

批量操作

高级查询

  1. 调用类的静态方法 ：@class@method(args) SELECT * FROM USER WHERE mobile_phone = '${@net.ytoec.framework.security.EncryptionDecryption@dSecret(userName)}'

  2. 调用类的静态方法 ：@class@method(args) SELECT * FROM USER WHERE mobile_phone = '${@net.ytoec.framework.security.EncryptionDecryption@dSecret(userName)}'

3、 mybatis的if 标签中，test 也是可以调用自定义的方法的

olap oltp

oltp 联机事务处理

olap 联机分析处理

sql

DQL:数据查询语言，用于对数据进行查询，如select

DML:数据操作语言，对数据进行增加、修改、删除，如insert、update、delete。

TPL: 事务处理语言，对事务进行处理，包括begin transaction、commit、rollback。

DCL:数据控制语言，进行授权与权限回收，如grant、revoke

DDL:数据定义语言，进行数据库、表的管理等，如create、drop

mysql json

select JSON_EXTRACT(xxx, '$**.settings')  from xxx where  xxx REGEXP 'setting'  ;



where JSON_CONTAINS_PATH(xxxx,'one','$.aa')>0


->：获取JSON文档的指定成员。

->>：获取JSON文档的指定成员，并将其作为无引号的字符串返回。

JSON_EXTRACT(json_doc, path)：提取JSON文档中的数据。

JSON_SET(json_doc, path, val)：更新JSON文档中的数据。

JSON_INSERT(json_doc, path, val)：向JSON文档中插入数据，如果路径已存在，则不进行任何操作。

JSON_REPLACE(json_doc, path, val)：替换JSON文档中的数据。

JSON_REMOVE(json_doc, path)：从JSON文档中删除数据。

更新于:

note

Copyright © w