mysql

JerryXia 发表于 2017-07-09 18:33:33 , 阅读 (2)

/* */ # 块注释-- # 行注释mysql -h127.0.0.1 -uroot -proot -p 2207 -D test # host user passwd port Database# basicSHOW CREATE TABLE xx; # 表结构DESC xx;CREATE TABLE yy LIKE xx; # 这样才能保持 表结构 + 索引INSERT yy SELECT * FROM xx ORDER BY col_xx; # 多进程插入时防死锁，主要 ORDER BY 字段要唯一，不然还是可能死锁ALTER TABLE xx ADD COLUMN xx_col  bigint(20) NOT NULL DEFAULT 0 COMMENT 'xx' AFTER yy_col;ALTER TABLE xx MODIFY COLUMN xx_col  bigint(20) NOT NULL DEFAULT 0 COMMENT 'xx' AFTER yy_col;ALTER TABLE xx DROP COLUMN xx_col;ALTER TABLE xx RENAME TO yy;# advancedINSERT IGNORE xx ON DUPLICATE KEY UPDATE # INSERT 要注意主键；IGNORE 重复时忽略；on duplicate key update 重复时更新SHOW PROCESSLIST; # 查看连接到 mysql 的进程EXPLAIN sql # sql 分析# functionconcat() # 其中一个为 null, 那么结果就是 null；拼接非字符串时, 需要配合 convert(num, char)# 自定义函数CREATE FUNCTION vipLevel (amount INT)RETURNS int(10)BEGIN    DECLARE vip int(10);    IF amount >=1000 THEN        SET vip=2;    ELSEIF amount>=600 THEN        SET vip=1;    ELSE        SET vip=0;    END IF;    RETURN vip;END;-- unicode 转 utf-8：https://gist.github.com/joni/2956080DROP FUNCTION STRINGDECODE;CREATE FUNCTION STRINGDECODE(str TEXT CHARSET utf8)RETURNS text CHARSET utf8 DETERMINISTICBEGINdeclare pos         int;declare escape      char(6) charset utf8;declare unescape    char(3) charset utf8;set pos = locate('\u', str);while pos > 0 do    set escape = substring(str, pos, 5);    set unescape = char(conv(substring(escape,2),16,10) using ucs2);    set str = replace(str, escape, unescape);    set pos = locate('\u', str, pos+1);end while;return str;END;SELECT STRINGDECODE("\u9648\u5fd7\u6797")# 导入、导出mysql -uroot -proot < /path/to/xxx.sql # 导入数据mysql> source /path/to/xxx.sqlmysqldump /*login*/ database > xxx.sql # 导出mysqldump /*login*/ database < xxx.sql # 导入if(col1=1,col1,0) as int_1 # 行列转换# mysql 允许外网访问bind-address # 修改 my.cnfupdate user set host='%' where user='root' # 修改 mysql.user 表GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.102' IDENTIFIED BY 'mypwd' WITH GRANT OPTION # 设定特定的连接flush privileges # 如果不执行这句, 需要重启 mysqld# 跳过密码验证登录skip-grant-tables       # 修改 my.cnf, 被一个没有密码的登录账号给坑了好长时间

mysql 的日常

如果有自增 id，多考虑使用自增 id，比如使用 id 查询最后几条数据
增删改应要当心，切记是否需要备份；能不删除尽量不删除，有种做法是表中添加 soft_delete 字段标识记录是否删除
交换2列的值: join 一下自己
tinyint(1)：表示一个字节, 数据范围是 0-255(unsigned), 所以用来表示数据类型完全够用, 基本不太可能会出现超出 255 种类型的
枚举类型: 缺点很明显, 添加新字段的时候需要修改表结构, 所以建议使用 varchar() 来替换, 当然如果程序写的不好, 添加新的类型的时候, 还是会很麻烦; 忽略大小写; 输入不在范围的值默认使用第一个
如果数字的小数位数是固定的，可以考虑放大后使用 int 来存储，比如金额精确到分以元为单位就是固定 2 位小数
signed: 需要计算，并且结果可能为负数，大部分场景都是用 unsigned
sql执行时只能使用一个索引
索引最左原则: 只有最左边的元素出现在查询中, 索引才会被用到, 所以应该把 where 中使用最多的一列放到最左边
union all/or/in 的效率分析: 有索引时union all相对会快一点, 取决于索引查询的时间, 没有时都是全表扫描, all/or 要快一些
减少需要执行 sql 的数量, 尽量一次取出尽可能多的数据
like / regexp 效率: like 在左端不固定的场景下, 不会使用到索引, 所以我喜欢使用 regexp
orderby / groupby: sql 慢的时候考虑先排序/分组, 再 join
优化：1. 减少sql数量, 一个sql取得多个需要的数据；2. 索引是否得当(explain 走起)
先修改 DB，再更新代码
修改线上库：确认是否需要备份、至少审核一遍 sql、本地可以测试一定要本地测试一遍

统计相关

需要合并多列数据到一行时，可以考虑 设置字段默认值 + unique key + insert + insert on duplicate update
mysql 按照日、周、月统计，周的周期从周五-周四 group by DATE_FORMAT(DATE_ADD(created_time,INTERVAL 3 DAY),'%Y%u') -> 注意偏移量
按照人来去重，比如一个人下了 5 单，一单支付成功，转化率就是 100%，需要使用 case-when 将用户路径量化，然后分组取最大值

[ 技术文章推荐 ]