MySQL

连接 MySQL

连接本机

mysql -u root -p

连接远程

mysql -h 110.110.110.110 -u root -p

指定字符集

mysql -u root -p --default-character-set=utf8mb4

常用命令

显示当前进程列表

SHOW PROCESSLIST;

显示会话变量

SHOW VARIABLES;
SHOW SESSION VARIABLES;
SHOW SESSION VARIABLES LIKE 'event_scheduler';

设置会话变量

SET wait_timeout = 10;
SET SESSION wait_timeout = 10;

显示系统变量

SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';

设置全局变量

SET GLOBAL wait_timeout = 10;

显示当前数据库服务器中的数据库列表

SHOW DATABASES;

显示当前用户

SELECT current_user;

切换至某数据库

USE `dbname`;

显示数据表清单

SHOW TABLES;

显示数据表的结构

DESCRIBE `tblname`;

导入 SQL

SOURCE d:/mysql.sql;

刷新权限

FLUSH PRIVILEGES;

表操作

分析表

ANALYZE TABLE `tblname`;

优化表（会锁定表）

OPTIMIZE TABLE `tblname`;

检查表

CHECK TABLE `tblname`;

校验表

CHECKSUM TABLE `tblname`;

修复表

REPAIR TABLE `tblname`;

快速修复表（重建索引）

REPAIR TABLE `tblname` QUICK;

清空表

TRUNCATE TABLE `pre_template_widget`;

删除表

DROP TABLE `tblname`;

查询数据

显示表中的记录

SELECT * FROM `tblname`;

联合查询

SELECT a.*, b.* FROM `tbl_a` AS a LEFT JOIN `tbl_b` AS b ON a.id = b.id;

IF CASE 运算

SELECT IF (priv=1, admin, guest) AS usertype FROM `tblname` WHERE username = 'joe';

IF NULL 运算

SELECT IFNULL( SUM(money), 0 ) AS total, COUNT(*) AS stats FROM `tblname` WHERE category = 'referral'；

GROUP BY 分组查询

SELECT name, SUM(price) FROM `tblname` GROUP BY name;

BETWEEN 查询

SELECT name, SUM(price) FROM `tblname` WHERE `month` BETWEEN 1 AND 12;

区分大小写查询

SELECT * FROM `tblname` WHERE BINARY columnA = 'abc';

截取部分字符串，查询超长文本会显著提高效率

SELECT event_id, taking_time, memory_usage, SUBSTR(result, 1, 200) as result FROM `pre_cron_event`;

字符串出现次数，将出现的字符串给替换成了空，然后得出长度差

SELECT SUM( LENGTH( extend ) - LENGTH( REPLACE( extend, ':', '' ) ) ) FROM `pre_weixin_effect` WHERE `created_date` = 20231016 AND `platform` = 'taobao';

分组查询并去重复

SELECT admin_id, SUM( money ) FROM pre_tasks_list GROUP BY admin_id;

SELECT admin_id, SUM( DISTINCT admin_id ) FROM pre_tasks_list GROUP BY admin_id;

SELECT admin_id, COUNT(*) AS stats, ( SELECT admin_name FROM `pre_system_admin` AS a WHERE a.admin_id = b.admin_id ) AS admin_name FROM `pre_finance_expend` as b GROUP BY b.admin_id ORDER BY stats DESC;

按状态值统计数量

SELECT `status`, COUNT(*) AS stats FROM `pre_taobao_adzone` GROUP BY `status`;

返回第一个非 NULL 的值

SELECT COALESCE(NULL, NULL, 1);

返回一个字符串结果，该结果由分组中的值连接组合而成

注意：mysql 的 group_concat_max_len 参数会影响运行结果

SELECT GROUP_CONCAT(id) AS ids FROM pre_portal_category WHERE status > 0 AND parent = 10;

SELECT GROUP_CONCAT(id SEPARATOR '|') AS ids FROM pre_portal_category WHERE status > 0 AND parent = 10;

SELECT GROUP_CONCAT(id ORDER BY id DESC SEPARATOR ',') AS ids FROM pre_portal_category WHERE status > 0 AND parent = 10;

SELECT COALESCE(GROUP_CONCAT(id ORDER BY id DESC SEPARATOR ','),0) AS ids FROM pre_portal_category WHERE status > 0 AND parent = 10;

SELECT GROUP_CONCAT(goods_id SEPARATOR ',') AS ids FROM ( SELECT goods_id FROM `pre_goods_list` WHERE `status` = 1 AND `cate_route` IS NULL ORDER BY id DESC LIMIT 40 ) AS tmp;

同时使用 GROUP BY 和 ORDER BY

SELECT * FROM ( select id, agent_id, agent_name , SUM(money) AS total FROM `pre_agent_trade` WHERE money > 0 AND `status` > 0 GROUP BY agent_id ) as t ORDER BY total DESC LIMIT 10;

多个字段 GROUP BY

SELECT SUM(total) FROM ( SELECT * FROM `pre_jingdong_deduct` WHERE id > 0 GROUP BY trade_id, sku_id) AS t;

取分组第一条记录

SELECT * FROM `pre_market_version` WHERE id IN( SELECT MAX(id) FROM `pre_market_version` GROUP BY `appid` );

查询重复记录

SELECT email, COUNT(email) AS q FROM `emails_table` GROUP BY email HAVING q > 1 ORDER BY q DESC;

销售额前100的代理

SELECT t.agent_id, t.agent_name, a.qq, SUM(t.money) AS total FROM `pre_agent_trade` as t left join pre_agent_list as a on a.agent_id = t.agent_id WHERE t.money > 0 GROUP BY t.agent_id ORDER BY total DESC LIMIT 100;

返回得分最高的 20 条记录，每个用户保留一条记录

SELECT raw.userid, raw.username, tmp.score, MIN(time) time FROM (SELECT userid, MAX(score) score FROM `pre_testing_record` WHERE activity_id = ? GROUP BY userid) tmp JOIN `pre_testing_record` raw ON raw.userid = tmp.userid AND tmp.score = raw.score WHERE activity_id = ? GROUP BY tmp.userid, tmp.score ORDER BY tmp.score DESC, time ASC, raw.record_id ASC LIMIT 20;

左连接的表中有多条数据，只取按时间排序最大的一条

SELECT c.*, g.goods_name, g.goods_short, g.goods_thumb, g.attr_price FROM `pre_goods_list` AS g INNER JOIN `pre_goods_comment` AS c ON c.goods_id = g.goods_id WHERE g.goods_id > 0 AND g.attr_comment > 0 AND c.comment_id = (SELECT MAX(comment_id) FROM `pre_goods_comment` WHERE goods_id = g.goods_id);

最近7天，每个时段查询

SELECT FROM_UNIXTIME(dateline,'%H') AS hh, appid, `version`, COUNT(*) FROM pre_app_statis WHERE `datetime` > DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY hh, appid, `version`;

最近7天，每个版本查询

SELECT `datetime`, appid, `version`, COUNT(*) FROM pre_app_statis WHERE appid = 1 AND `datetime` > DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY `datetime`, `version`;

提现次数大于 2 笔的用户

SELECT agent_id, agent_name, `money`, `datetime`, COUNT(*) AS stat FROM `pre_agent_trade` WHERE `money` < 0 and `datetime` >= 20160720 GROUP BY agent_id HAVING stat > 1;

查找重复的卡密

SELECT agent_id, agent_name, code, COUNT(*) AS stat FROM `pre_agent_cdkey` GROUP BY code HAVING stat > 1;

新注册代理付款金额

SELECT t.sn, t.id, t.money, c.agent_id, c.agent_name FROM pre_agent_trade AS t LEFT JOIN ( SELECT * FROM `pre_agent_cdkey` WHERE trade_id > 0 ORDER BY trade_id ASC GROUP BY trade_id ) AS c on t.id = c.trade_id WHERE t.sn != '';

商品搜索页排序显示

SELECT `id`, `gid`, `title`, stick, uid, `dateline`, `timeline`, FROM_UNIXTIME( `timeline`, '%Y/%m/%d %T' ) FROM `pre_goods_list` WHERE `status` > 0 ORDER BY stick DESC, timeline DESC LIMIT 80, 40;

代理销售前100

SELECT t.agent_id, t.agent_name, l.qq, sum(t.money) AS stat FROM `pre_trade_list` AS t LEFT JOIN `pre_agent_list` AS l ON t.agent_id = l.agent_id GROUP BY t.agent_id ORDER BY stat DESC LIMIT 100;

连接多个字段

SELECT CONCAT(first_name,' ',last_name) AS full_name FROM `tblname`;

重复的渠道+广告位组合

SELECT CONCAT( relation_id, '_', adzone_id ) AS idx, COUNT(*) AS q FROM `pre_member_relation` WHERE relation_id > 0 GROUP BY idx HAVING q > 1;

移除小数后面的零

SELECT (TRIM(attr_price) + 0 ) AS attr_price;

移除前后的符号

SELECT TRIM(BOTH ', ' FROM @member_ids);

指定使用索引

SELECT id FROM `tblname` USE INDEX(type) WHERE type = 12345 AND level > 3 ORDER BY id;

SELECT * FROM `pre_order_list` USE INDEX(PRIMARY, create_date,settle_date,member_id,tk_status) WHERE `create_date` BETWEEN 20200301 AND 20200331 ORDER BY order_id DESC LIMIT 0,20;

SELECT SUM(money) AS money, COUNT(*) AS amount FROM `pre_order_deduct` USE INDEX(member_id, create_date) WHERE `member_id` = 355052 AND `create_date` BETWEEN 20200301 AND 20200331 AND `freeze` = 0;

使用全文索引

SELECT * FROM articles WHERE MATCH(content_column) AGAINST ('music');

查找以某字符开头的记录，LOCATE

SELECT * FROM `pre_gateway_traced` WHERE `status` = 1 AND LOCATE( `action`, '/api/' ) = 1 ORDER BY id DESC

严格比较两个 NULL 值是否相等

SELECT * FROM `tbl_a` A LEFT JOIN `tbl_b` B ON A.ID = B.ID WHERE A.column <=> B.column;

SELECT * FROM `tbl_a` A LEFT JOIN `tbl_b` B ON A.ID = B.ID WHERE NOT( A.column <=> B.column );

中文字符排序

SELECT * FROM `tblname` ORDER BY CONVERT(vender_abbrev USING gbk) ASC;

使用子查询结果来排序

SELECT sub.*, ( SELECT status FROM `pre_weixin_stick` WHERE sub.`id` = `source_id` AND `member_id` = 10008 ) AS weight FROM `pre_weixin_source` AS sub WHERE sub.`category` = 'groups' ORDER BY weight DESC, sub.`sort` DESC;

WHERE IN 同时保持排序（IN 适合于外表数据量大而内表数据小的情况）

SELECT * FROM `tblname` WHERE id IN(5,2,6,8,12,1) ORDER BY FIELD(id,5,2,6,8,12,1);

WHERE EXISTS（EXISTS 适合于外表小而内表大的情况）

SELECT num FROM `tblname` AS t WHERE EXISTS( SELECT 1 FROM `tblmisc` WHERE num = t.num );

查找以逗号分隔的值，IN 效率更好

SELECT * FROM `pre_pinduoduo_goods` WHERE FIND_IN_SET(10700, opt_ids);

SELECT COUNT(*) FROM `pre_member_client` WHERE FIND_IN_SET(`app_id`, '20860,20859,20858,20857,20856');

查找某字段不以某符号结尾的记录

SELECT * FROM `pre_article_list` WHERE id NOT IN ( SELECT id FROM `pre_article_list` WHERE article_content LIKE '%>' );

查找错误的用户头像地址

SELECT * FROM `pre_member_list` WHERE `avatar` != '' AND ( `avatar` NOT LIKE 'http:%' AND `avatar` NOT LIKE 'https:%' );

查找没有店铺信息的店铺ID

SELECT seller_id FROM `pre_goods_list` WHERE seller_id NOT IN ( SELECT seller_id FROM `pre_goods_shop` ) AND status = 1 AND seller_id > 0 ORDER BY id DESC LIMIT 100;

使用正则匹配

SELECT * FROM `pre_wechat_account` WHERE 'www.example.com' REGEXP domain;

按时段统计订单数量和金额

SELECT COUNT(*), SUM(pub_share_pre_fee), FROM_UNIXTIME( UNIX_TIMESTAMP( create_time ), '%H' ) AS hour FROM pre_order_shadow WHERE create_date >= 20191010 GROUP BY hour;

with rollup 分组统计数据的基础上再进行统计汇总

SELECT coalesce(name, '总金额') name, SUM(money) as money FROM order_diy GROUP BY name WITH ROLLUP;

资金流水余额

SELECT money, (SELECT SUM(money) FROM `pre_account_detail` WHERE id <= d.id AND `member_id` = 10008) AS balance FROM `pre_account_detail` AS d WHERE `member_id` = 10008;

同时拿到第一条和最后一条记录

SELECT `test`.* FROM `test` JOIN ( SELECT item, min(id) AS min_id, max(id) AS max_id FROM `test` GROUP BY ITEM ) AS tmp on id = tmp.min_id OR id = tmp.max_id;

第一条和最后一条之间的差值（注意 > 并且没有第二条记录时值为 NULL，>= 在没有第二条记录时，其实是与自己比较）

SELECT item, value, (SELECT value FROM `test` WHERE id >= d.id AND item = d.item ORDER BY id DESC LIMIT 1) AS last_value FROM `test` AS d GROUP BY ITEM;

SELECT item, value, (SELECT value FROM `test` WHERE id >= d.id AND item = d.item ORDER BY id DESC LIMIT 1) - value AS diff FROM `test` AS d GROUP BY ITEM;

两时间比较

SELECT TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');

SELECT DATEDIFF('2018-03-22 09:00:00', '2018-03-20 07:00:00');

使用 LEAST 函数找到多个字段的最小值

SELECT LEAST( ord.commission, ord.pub_share_pre_fee );

使用 GREATEST 函数找到多个字段的最大值

SELECT GREATEST( ord.commission, ord.pub_share_pre_fee );

当前时间戳

SELECT UNIX_TIMESTAMP();

某月总天数

SELECT DAY(LAST_DAY(CONCAT(months,'-01')));
SELECT DAY(LAST_DAY(DATE_FORMAT( create_time, '%Y%m01')));

获取字符串的 ASCII 值

SELECT LENGTH(exp), ORD(CONVERT(exp USING ucs2));

获取字符串的 UNICODE 值

SELECT LENGTH(exp), HEX(CONVERT(exp USING ucs2));

格式化时间

# 输出结果：2012
SELECT DATE_FORMAT(NOW(),'%Y') YEAR;

# 输出结果：12
SELECT DATE_FORMAT(NOW(),'%y') YEAR;

# 输出结果：11
SELECT DATE_FORMAT(NOW(),'%m') MONTH;

# 输出结果：15
SELECT DATE_FORMAT(NOW(),'%d') DAY;

# 输出结果：14:44:50
SELECT DATE_FORMAT(NOW(),'%T') TIME;

# 输出结果：2012-11-15
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') DATE;

# 输出结果：2012-11-15-14:46:57
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d-%T') DATETIME;

# 输出结果：20180622
SELECT FROM_UNIXTIME( 1529640863, '%Y%m%d');

# 15天前的时间
SELECT CURRENT_DATE - INTERVAL 15 DAY;

# 1个月前的时间
SELECT CURRENT_DATE - INTERVAL 1 MONTH;

# 15天前的时间戳
SELECT UNIX_TIMESTAMP( CURRENT_DATE - INTERVAL 15 DAY );

# 15天前的格式化时间
SELECT DATE_FORMAT( CURRENT_DATE - INTERVAL 15 DAY, '%Y%m%d' );

标志位运算（1,2,4,8,16,32）

查询语句

SELECT * FROM `pre_member_relation` WHERE locked & 2;

所有下级总裁 (90%)拥有升级权限

SELECT id, member_id, upgrade_code FROM `pre_member_relation` WHERE `upgrade_code` & 16 = 0 AND `upgrade_code` & 8 LIMIT 10;

所有下级总裁 (80%)拥有升级权限

SELECT id, member_id, upgrade_code FROM `pre_member_relation` WHERE `upgrade_code` & 16 AND `upgrade_code` & 8 = 0 LIMIT 10;

增加标志

UPDATE `pre_member_relation` SET locked = locked | 4 WHERE locked & 4 = 0;

移除标志

UPDATE `pre_member_relation` SET locked = locked ^ 8 WHERE locked & 8;

JOIN 优化

LEFT JOIN

MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据

RIGHT JOIN

MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据

INNER JOIN

MySQL INNER JOIN 获取两个表中字段匹配关系的记录

FULL JOIN

MySQL FULL JOIN 当左表或右表中有匹配项时，返回所有记录

插入数据

INSERT INTO `tblname` VALUES ('a','b');

插入多条记录

INSERT INTO `tblname` (`order_sn`) VALUES ('190730035448751133473'), ('190730386700411711658');

从一个表插入另外一个表

INSERT INTO `tblname` (field1,field2,field3) SELECT newfield1,newfield2,'fixed value' FROM `tblname2`;

将过期商品拷贝到备份表

REPLACE INTO `pre_goods_backup` SELECT * FROM `pre_goods_list` WHERE `status` = 0 AND `coupon_expire` - today <= valid;

删除和更新

删除数据

DELETE FROM `tblname` WHERE site_id = 46;

带查询的删除

DELETE FROM `tblname` WHERE gid IN( SELECT gid FROM `pre_goods_list` WHERE `status` = 0 AND `coupon_expire` - today <= valid );

更新数据

UPDATE `tblname` SET field1 = 'a', field2 = 'b' WHERE field3 = 'c';

带条件更新

UPDATE `tblname` SET leader = IF( id in (1,2), 1, 0) WHERE gid = 1 LIMIT 10;

在0或1之间切换

UPDATE `tblname` SET field = 1 - field;

字段内容替换

UPDATE `tblname` SET screen = REPLACE( screen, 'mopland.com', 'guodongbaohe.com' );

带查询的更新

UPDATE pre_tasks_list SET payout_quantity =( SELECT COUNT(*) FROM pre_tasks_order WHERE pre_tasks_order.status = 1 AND pre_tasks_list.id = pre_tasks_order.tasks_id );

更新订单表商品主图

UPDATE `pre_order_list` SET goods_thumb = ( SELECT goods_thumb FROM `pre_goods_list` WHERE goods_id = `pre_order_list`.num_iid ) WHERE goods_thumb IS NULL;

更新订单表来源字段

UPDATE `pre_order_list` SET source = ( SELECT IF ( EXISTS( SELECT 1 FROM `pre_goods_list` WHERE goods_id = `pre_order_list`.num_iid ), 1, 2) ) WHERE source = 0;

更新和查询同一张表

UPDATE `pre_member_relation` r INNER JOIN (SELECT `jingdong_uid`, `member_id`, `source_id` FROM `pre_member_relation`) b ON r.`source_id` = b.`member_id` SET r.`jingdong_uid` = b.jingdong_uid WHERE r.`jingdong_uid` IS NULL;

同时 JOIN 更新多个字段

UPDATE `pre_member_upgrade` AS upg JOIN `pre_member_relation` AS rel ON rel.`member_id` = upg.member_id SET upg.source_id = rel.source_id, upg.source_name = rel.source_name WHERE upg.source_id IS NULL;

保留一条记录，删除其他重复数据

DELETE FROM `tblname` WHERE id NOT IN( SELECT ids FROM ( SELECT MAX(id) AS ids FROM `tblname` GROUP BY openid ) AS tmp );

DELETE FROM `pre_member_beian` WHERE beian_id NOT IN( SELECT MIN(beian_id) AS ids FROM `pre_member_beian` GROUP BY relation_id );

上架已通过审核的商品

UPDATE `pre_goods_list` SET status = 1 WHERE gid IN ( SELECT gid FROM `pre_member_goods` WHERE status = 1 );

补全 URL 协议

UPDATE `pre_member_list` SET avatar = CONCAT( 'https:', avatar ) WHERE SUBSTRING( avatar, 1, 2 ) = '//';

更新 Ymd 日期

UPDATE `pre_member_upgrade` SET `datetime` = FROM_UNIXTIME(`dateline`, '%Y%m%d');

UPDATE `pre_order_deduct` SET settle_date = DATE_FORMAT( settle_time, '%Y%m%d') WHERE `settle_time` IS NOT NULL AND `settle_date` = 0;

借助临时字段交换两列的值

UPDATE `pre_widget_data` SET tmp = extend, extend = link, link = tmp, tmp = NULL WHERE extend LIKE '%:%';

批量更新

REPLACE INTO

REPLACE INTO `tblname`(id, dr) VALUES (1,'2'),(2,'3'),('n','y');

INSERT INTO

INSERT INTO `tblname`(id, dr) VALUES (1,'2'),(2,'3'),…('x','y') ON DUPLICATE KEY UPDATE dr = VALUES(dr);

WHEN x THEN y

UPDATE tbl SET status = CASE id 
    WHEN 1 THEN 3
    WHEN 2 THEN 4
    WHEN 3 THEN 5
END WHERE id IN (1,2,3);

结构更改

建表结构

SHOW CREATE TABLE `tblname`;

重建数据表

TRUNCATE TABLE `tblname`;

更改表引擎

ALTER TABLE `tblname` ENGINE = innodb;

创建数据库

CREATE DATABASE `dbname`;

复制表结构

CREATE TABLE `tblname` LIKE `users`;

复制表结构和数据（不带索引）

CREATE TABLE `tblname` [AS] SELECT * FROM `users`;

备份表不存在时，复制表结构

CREATE TABLE IF NOT EXISTS `pre_goods_backup` LIKE `pre_goods_list`;

完全复制表（带完整结构和数据）

CREATE TABLE `pre_service_version` LIKE `pre_gateway_version`;
INSERT INTO `pre_service_version` SELECT * FROM `pre_gateway_version`;

创建数据表

CREATE TABLE `tblname` (
  `id` int(11) NOT NULL auto_increment,
  `fid` int(11) NOT NULL,
  `appid` varchar(15) NOT NULL,
  `uid` int(11) NOT NULL,
  `username` varchar(30) default NULL,
  `config` text,
  `state` tinyint(4) NOT NULL,
  `dateline` int(11) NOT NULL,
  `ip` varchar(15) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fid` (`fid`),
  KEY `appid` (`appid`),
  KEY `uid` (`uid`),
  KEY `username` (`username`)
) ENGINE=MyISAM;

重命名表名

ALTER TABLE `tblname` RENAME `abc`;

更改表名

RENAME TABLE `oldname` TO `tblname`;

修改字段类型

ALTER TABLE `tblname` MODIFY `field` VARCHAR(255) NULL DEFAULT NULL;

修改字段区分大小写

ALTER TABLE `tblname` MODIFY `url` VARCHAR(700) BINARY NOT NULL;

同时修改字段名和类型

ALTER TABLE `tblname` CHANGE `new` `old` VARCHAR(255) NULL DEFAULT NULL;

新增字段

ALTER TABLE `tblname` ADD `new` INT NOT NULL AFTER `abc`;

新增索引

CREATE INDEX `state` ON `tblname` (`state`);

CREATE UNIQUE INDEX `state` ON `tblname` (`state`);

删除主键

ALTER TABLE `tblname` DROP PRIMARY KEY;

删除主键，去自增

ALTER TABLE `tblname` MODIFY id INT, DROP PRIMARY KEY;

删除表

DROP TABLE IF EXISTS `tblname`;

删除字段

ALTER TABLE `tblname` DROP `field`;

删除索引

ALTER TABLE `tblname` DROP INDEX `field`;

整表转编码

ALTER TABLE `tblname` CONVERT TO CHARACTER SET utf8;

字段大小写

Collate 校对规则

*_bin: binary case sensitive collation，区分大小写
*_cs: case sensitive collation，区分大小写
*_ci: case insensitive collation，不区分大小写

触发器操作

新增触发器

CREATE TRIGGER `tasks_order_totals` AFTER INSERT ON `pre_tasks_order` FOR EACH ROW
BEGIN
    UPDATE pre_tasks_dist set `total` = `total` + 1, `money` = `money` + new.money WHERE tasks_id = new.tasks_id;
END;;

触发器时机

AFTER UPDATE    更新时
BEFORE UPDATE   更新前

AFTER INSERT    插入时
BEFORE INSERT   插入前

AFTER DELETE    删除时
BEFORE DELETE   删除前

单个变量定义

DECLARE stats INT;

SET stats = ( SELECT COUNT(*) FROM pre_tasks_order WHERE tasks_id = new.tasks_id AND taoke_id = old.taoke_id AND status = 1 LIMIT 1 );
UPDATE pre_tasks_dist set valid = stats WHERE tasks_id = old.tasks_id AND taoke_id = old.taoke_id;

多个变量赋值

DECLARE stats INT;
DECLARE total decimal(10,2);

SELECT COUNT(*),SUM(money) INTO @stats,@total FROM pre_tasks_order WHERE tasks_id = new.tasks_id AND taoke_id = old.taoke_id AND status = 1;
UPDATE pre_tasks_dist set `valid` = @stats, `money` = @total WHERE tasks_id = old.tasks_id AND taoke_id = old.taoke_id;

删除触发器

DROP TRIGGER `tasks_order_totals`;

存储过程

定义方法

DELIMITER ;;
DROP PROCEDURE IF EXISTS `func_tasks_save`;;
CREATE PROCEDURE `func_tasks_save`(IN `task` varchar(64), IN `data` text, IN `stamp` INT(11))
BEGIN

    -- 写入任务日志
    INSERT INTO `pre_system_tasks`(`task`,`data`,`duration`,`started_time`,`created_time`,`created_date`) VALUES( task, data, UNIX_TIMESTAMP() - stamp, stamp, UNIX_TIMESTAMP(), DATE_FORMAT(NOW(),'%Y%m%d') );

END;;

DELIMITER ;

调用方法

CALL func_tasks_save( 'todo_minute_task', '', UNIX_TIMESTAMP() );

定时任务

DELIMITER ;;
CREATE EVENT `todo_fivemin_task`
ON SCHEDULE EVERY '5' MINUTE STARTS '2016-05-18 00:00:05' ON COMPLETION PRESERVE
ENABLE COMMENT '' DO
BEGIN

    -- 任务开始时间
    SET @start_time = UNIX_TIMESTAMP();

    --

    -- 更新备案渠道使用数
    UPDATE `pre_taobao_beian` SET binding_num = ( SELECT COUNT(*) FROM `pre_member_relation` WHERE relation_id = `pre_taobao_beian`.relation_id );
    SELECT ROW_COUNT() INTO @row_count;

    -- 写入任务日志
    CALL func_tasks_save( 'todo_fivemin_task', @row_count, @start_time );

END;;
DELIMITER ;

视图操作

创建视图

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `pre_tasks_order_extend` AS SELECT o.*, tk.taoke_name FROM `pre_tasks_order` AS o LEFT JOIN `pre_taoke_list` AS tk ON o.taoke_id = tk.taoke_id;

跨库建视图

CREATE VIEW `pre_agent_config` AS select * FROM `tps`.`pre_agent_config`;

删除视图

DROP VIEW `pre_tasks_order_extend`;

操作用户

指定主机

GRANT ALL PRIVILEGES ON *.* TO qcloud@localhost IDENTIFIED BY 'password' WITH GRANT OPTION;

任何主机

GRANT ALL PRIVILEGES ON *.* TO qcloud@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

修改用户密码

ALTER USER test_user IDENTIFIED BY 'password';

修改当前登录用户

ALTER USER USER() IDENTIFIED BY 'password';

使密码过期

ALTER USER test_user IDENTIFIED BY 'password' PASSWORD EXPIRE;

使密码从不过期

ALTER USER test_user IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;

按默认设置过期时间

ALTER USER test_user IDENTIFIED BY 'password' PASSWORD EXPIRE DEFAULT;

指定密码过期间隔

ALTER USER test_user IDENTIFIED BY 'password' PASSWORD EXPIRE INTERVAL 90 DAY;

事件调度器

全局开启

SET GLOBAL event_scheduler=1;

创建方法

DELIMITER $$
CREATE EVENT `test_event`
ON SCHEDULE
    EVERY 1 MINUTE
    ON COMPLETION PRESERVE
DO
BEGIN
    INSERT INTO `tblname`(`time`) VALUES (NOW());
END $$  
DELIMITER ;

关闭事件

ALTER EVENT `test_event` DISABLE;

开启事件

ALTER EVENT `test_event` ENABLE;

执行一次

ALTER EVENT `test_event` ON COMPLETION NOT PRESERVE;

持久执行

ALTER EVENT `test_event` ON COMPLETION PRESERVE;

删除事件

DROP EVENT IF EXISTS `test_event`;

性能优化

mysql 数据按条件导出

# 仅导出部分数据
mysqldump -hlocalhost -uuser -p --skip-triggers --no-create-info dbname tbname -w "id < 1000"  > /path/to/filename.sql

# 仅导出结构
mysqldump -uuser -ppass dbname -d --skip-add-drop-table --skip-comments --add-drop-database > /path/to/dbname.sql
mysqldump -uuser -ppass dbname -d --skip-add-drop-table --skip-comments > /path/to/dbname.sql

# 参数说明
-d 只生成创建表结构的语句
-t 只生成插入数据的语句

为查询缓存优化你的查询

<?php
// 查询缓存不开启
$result = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

// 开启查询缓存
$today = date("Y-m-d");
$result = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

EXPLAIN 你的 SELECT 查询

EXPLAIN SELECT COUNT(*) FROM `pre_member_goods` WHERE `status` = 0 AND uid = 12003;

当只要一行数据时使用 LIMIT 1

SELECT * FROM `pre_member_goods` WHERE `status` = 0 AND uid = 12003 LIMIT 1;

为搜索字段建索引

ALTER TABLE `pre_member_goods` ADD INDEX `status` (`status`);

在 JOIN 表的时候使用相当类型的例，并将其索引

<?php
$sql = "SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id";

$result = mysql_query( $sql );

千万不要 ORDER BY RAND()

<?php
// 千万不要这样做
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

// 这要会更好
$r = mysql_query("SELECT COUNT(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);

$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

避免 SELECT *

SELECT goods_id, goods_name FROM `pre_member_goods` WHERE `status` = 0 AND uid = 12003 LIMIT 1;

永远为每张表设置一个ID

ALTER TABLE `test` ADD `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

可省略 ORDER BY id ASC

# 加上 id ASC 可能触发索引
SELECT * FROM `pre_member_goods` WHERE `status` = 0 ORDER BY id ASC;

# Mysql 默认使用 PRIMARY ASC 排序（升序），所以可以省略
SELECT * FROM `pre_member_goods` WHERE `status` = 0;

将影响索引的 GROUP BY 稍后执行

# Bad
SELECT `item_id` FROM `pre_order_list` WHERE platform = 'kaola' AND `created_date` >= 20210510 AND `item_pic` = '' GROUP BY item_id LIMIT 100;

# Good
SELECT * FROM ( SELECT `item_id` FROM `pre_order_list` WHERE platform = 'kaola' AND `created_date` >= 20210510 AND `item_pic` = '' ) AS tmp GROUP BY item_id LIMIT 100;

使用准确的 WHERE 条件

# Bad
SELECT * FROM `pre_order_list` WHERE `order_code` != 13;

# Good
SELECT * FROM `pre_order_list` WHERE `order_code` IN( 3, 12, 14 );

使用准确的字段类型

# Bad
SELECT * FROM `pre_order_list` WHERE `trade_id` = 62465555;

# Good
SELECT * FROM `pre_order_list` WHERE `trade_id` = '62465555';

多个字段参与排序，确保结果恒等于

# Bad
SELECT item_id, COUNT(*) AS stats FROM `pre_order_list` GROUP BY item_id ORDER BY stats DESC;

# Good
SELECT item_id, COUNT(*) AS stats FROM `pre_order_list` GROUP BY item_id ORDER BY stats DESC, create_time DESC;

优先对 JOIN 的表进行条件过滤

# Bad
SELECT COUNT(ord.order_id) FROM `pre_order_list` AS ord LEFT JOIN `pre_taobao_token` AS tkl ON ord.member_id = tkl.member_id AND ord.item_id = tkl.goods_id WHERE ord.`platform` = 'taobao' AND ord.created_date = 20210513 AND tkl.created_date = 20210513;

# Good
SELECT COUNT(ord.order_id) FROM `pre_order_list` AS ord LEFT JOIN `pre_taobao_token` AS tkl ON tkl.created_date = 20210513 AND ord.member_id = tkl.member_id AND ord.item_id = tkl.goods_id WHERE ord.`platform` = 'taobao' AND ord.created_date = 20210513;

把IP地址存成 UNSIGNED INT

使用 INET_ATON() 来把一个字符串IP转成一个整形，并使用 INET_NTOA() 把一个整形转成一个字符串IP

而在PHP中，使用函数 ip2long() 和 long2ip()

拆分大的 DELETE 或 INSERT 语句

DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000;

按季度拆分大数据表，参考 backup_order

7月备份 1~3 月，10月备份 4~6 月，1月备份 7~9 月，4月备份 10~12 月

设计数据表的建议

表名、列名长度不超过 16
每个字段都必须有 NOT NULL DEFAULT ''， 如果是 int 则 default 0
主键必须为 int/bigint 类型
如果是 int 类型，且不会存负数，则标记为 UNSIGNED INT
如果 int 类型是 10 以下的几个可枚举值，则使用 TINYINT 类型
varchar 长度小于 3000
text 字段个数不超过 3 个
每个字段增加 COMMENT 注释，说明字段用途
索引不能有重复
索引个数不能大于 5 个（包括主键）
索引字段必须为 not null，并且有 default 值
请不要使用 MySQL 保留字

配置说明

配置文件

/etc/my.cnf

[client]

# 客户端默认端口和密码
port = 3306
password=root

# 更改 sock 位置
socket=/disk/mysql/mysql.sock

# 同时更新 /etc/php.ini 
mysql.default_socket = /disk/mysql/mysql.sock
mysqli.default_socket = /disk/mysql/mysql.sock
pdo_mysql.default_socket = /disk/mysql/mysql.sock

[mysqld]

# 更改 Data 目录
datadir=/disk/mysql

# 更改 Sock 文件
socket=/disk/mysql/mysql.sock

# 等待超时（设置后会影响密集查询）
wait_timeout = 10

# 启用事件调度器
event_scheduler = ON

# 修复表进程
myisam_repair_threads = 8

# 最大连接数
max_connections = 10240

# 最大错误连接数（超过此值会导致拒绝连接请求）
max_connect_errors = 1024

# 连接的非NULL值的字符串长度
group_concat_max_len = 102400

# 慢查询时间限定
long_query_time = 1

# 开启慢查询日志
slow_query_log = 1

# 慢查询日志文件
slow_query_log_file = /var/log/mysql.slow.log

# 开启普通日志
general_log = ON

# 普通日志文件
general_log_file = /var/log/mysql.general.log

# 绑定 IP 地址
bind-address = 127.0.0.1

# 禁止DNS反向解析（只使用IP连接，可以提升远程连接速度）
skip-name-resolve = 1

# 跳过权限验证表（常用于修改 root 密码）
skip-grant-tables = 1

# 默认存储引擎
default-storage-engine = InnoDB

# 独立表空间
innodb_file_per_table = 1

# 缓冲池字节大小（内存的 70%-80%）
innodb_buffer_pool_size = 6G

# 日志组每个日志文件的字节大小
innodb_log_file_size = 256M

# 写入日志文件时的缓冲区大小
innodb_log_buffer_size = 8M

# 等待事务锁超时时间
innodb_lock_wait_timeout = 60

# sql_mode 配置
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

常用 sql_mode

# ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

# NO_AUTO_VALUE_ON_ZERO
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

# STRICT_TRANS_TABLES
在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制

# NO_ZERO_IN_DATE
在严格模式下，不允许日期和月份为零

# NO_ZERO_DATE
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

# ERROR_FOR_DIVISION_BY_ZERO
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL

# NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户

# NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

# PIPES_AS_CONCAT
将”||”视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

# ANSI_QUOTES： 
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

常见错误

使用 source 导入数据报错 ERROR:Unknown command '\U'. ERROR:Unknown command '\A'. ERROR:Unknown command '\D'.

# 指定默认编码
mysql -uroot -p --default-character-set=utf8

# 指定编码和库
mysql -uroot -p --default-character-set=utf8 dbname < test_service.sql

MYSQL REPLACE INTO 和 INSERT INTO 的区别

1. 表必须有主键或者是唯一索引,否则没有什么不同；
2. 如果有主键或者是唯一索引，则REPLACE发现重复的先删除再插入，如果记录有多个字段，在插入的时候如果有的字段没有赋值，那么新插入的记录这些字段为空，且返回的值为删除的条数和插入的条数之和；而insert 发现重复的则报错。

Table 'performance_schema.session_status' doesn't exist

mysql_upgrade -u root -p --force

SQLSTATE[HY000] [1129] is blocked because of many connection errors

mysqladmin -uroot -p flush-hosts

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

# Mysql 5.6 以前
SELECT COUNT(*) as `stats`,`action` as `name`,`module` FROM `pre_order_list` GROUP BY `module` ORDER BY `stats` DESC

# Mysql 5.7 以后，需要将更多的参数参与 GROUP BY 分类
SELECT COUNT(*) as `stats`,`action` as `name`,`module` FROM `pre_order_list` GROUP BY `action`, `module` ORDER BY `stats` DESC

Plugin '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' is not loaded

# 配置并重启 MySQL
skip-grant-tables = 1

# 使用 root 登录后执行
USE mysql;
UPDATE user SET authentication_string = password('root'), password_expired = 'N' WHERE user = 'root';
UPDATE user SET plugin = "mysql_native_password";
DELETE FROM user WHERE user = '';
FLUSH PRIVILEGES;
QUIT;

# 移除配置后重启 MySQL
#skip-grant-tables = 1

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

SET authentication_string = password('my_password') WHERE user = 'root';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

SET password = password('!XXXXX);

ERROR 1040(00000):Too many connections

表明云数据库实例当前最大连接数超过了限制。请检查程序，适当减少数据库的连接数。

批量修改表引擎（MyISAM 转 InnoDB）

SET @DATABASE_NAME = 'tblname';
SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

生成删除所有表语句间接现实清空数据库表

SELECT CONCAT('DROP TABLE IF EXISTS `', table_name, '`;') FROM information_schema.tables WHERE table_schema = 'dbname';

Table `tbl` doesn't exist && Table `tbl` already exists

1. CREATE TABLE `tbl_2`
2. Stop Mysql
3. Delete tbl.ibd
4. Copy `tbl_2.frm` to `tbl.frm`
5. Copy `tbl_2.ibd` to `tbl.ibd`
6. DROP TABLE `tbl` 
7. Delete tbl.ibd
8. Rename `tbl_2` to `tbl`

MySQL

连接 MySQL

连接本机

连接远程

指定字符集

常用命令

显示当前进程列表

显示会话变量

设置会话变量

显示系统变量

设置全局变量

显示当前数据库服务器中的数据库列表

显示当前用户

切换至某数据库

显示数据表清单

显示数据表的结构

导入 SQL

刷新权限

表操作

分析表

优化表（会锁定表）

检查表

校验表

修复表

快速修复表（重建索引）

清空表

删除表

查询数据

显示表中的记录

联合查询

IF CASE 运算

IF NULL 运算

GROUP BY 分组查询

BETWEEN 查询

区分大小写查询

截取部分字符串，查询超长文本会显著提高效率

字符串出现次数，将出现的字符串给替换成了空，然后得出长度差

分组查询并去重复

按状态值统计数量

返回第一个非 NULL 的值

返回一个字符串结果，该结果由分组中的值连接组合而成

同时使用 GROUP BY 和 ORDER BY

多个字段 GROUP BY

取分组第一条记录

查询重复记录

销售额前100的代理

返回得分最高的 20 条记录，每个用户保留一条记录

左连接的表中有多条数据，只取按时间排序最大的一条

最近7天，每个时段查询

最近7天，每个版本查询

提现次数大于 2 笔的用户

查找重复的卡密

新注册代理付款金额

商品搜索页排序显示

代理销售前100

连接多个字段

重复的渠道+广告位组合

移除小数后面的零

移除前后的符号

指定使用索引

使用全文索引

查找以某字符开头的记录，LOCATE

严格比较两个 NULL 值是否相等

中文字符排序

使用子查询结果来排序

WHERE IN 同时保持排序（IN 适合于外表数据量大而内表数据小的情况）

WHERE EXISTS（EXISTS 适合于外表小而内表大的情况）

查找以逗号分隔的值，IN 效率更好

查找某字段不以某符号结尾的记录

查找错误的用户头像地址

查找没有店铺信息的店铺ID

使用正则匹配

按时段统计订单数量和金额

with rollup 分组统计数据的基础上再进行统计汇总

资金流水余额

同时拿到第一条和最后一条记录

第一条和最后一条之间的差值（注意 > 并且没有第二条记录时值为 NULL，>= 在没有第二条记录时，其实是与自己比较 ）

两时间比较

使用 LEAST 函数找到多个字段的最小值

使用 GREATEST 函数找到多个字段的最大值

第一条和最后一条之间的差值（注意 > 并且没有第二条记录时值为 NULL，>= 在没有第二条记录时，其实是与自己比较）