MySQL 技巧

2016/01/01 Database

这里总结一些 MySQL 的奇巧淫技。

信息

库大小

# 整个 mysql 大小
select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024 as index_length,sum(data_length+index_length)/1024/1024 as sum from information_schema.tables;

# 各个库大小
select table_schema, sum(data_length+index_length)/1024/1024 as total_mb,
sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb, 
count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc; 

# 单个库大小
select concat(truncate(sum(data_length)/1024/1024,2),'mb') as data_size,  
concat(truncate(sum(max_data_length)/1024/1024,2),'mb') as max_data_size, 
concat(truncate(sum(data_free)/1024/1024,2),'mb') as data_free, 
concat(truncate(sum(index_length)/1024/1024,2),'mb') as index_size 
 from information_schema.tables where table_schema = 'erongtu_tyb2014';  

账号

# 创建用户 密码8位以上,包括:大写字母、小写字母、数字、特殊字符 %:匹配所有主机,该地方还可以设置成‘localhost’,代表只能本地访问,例如root账户默认为‘localhost
create user '[用户名称]'@'%' identified by '[用户密码]'
# 用户授权数据库 *代表整个数据库
grant select,insert,update,delete,create on [数据库名称].* to [用户名称]
# 立即启用修改
flush privileges
# 取消用户所有数据库(表)的所有权限
revoke all on *.* from tester
# 删除用户
delete from mysql.user where user='tester'
drop user admin@localhost
# 立即启用修改
flush privileges
# 授权所有权限
GRANT ALL PRIVILEGES ON *.* TO 'iappuser'@'localhost' IDENTIFIED BY 'iApp16User' WITH GRANT OPTION;

DDL

表相关

# 建表
CREATE TABLE COM (
  ID INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(255)
)ENGINE = InnoDB, DEFAULT CHARSET = utf8;
# 修改表名
ALTER TABLE COM RENAME COM1
# 修改注释
ALTER TABLE COM COMMENT '测试SQL语句';
# 修改引擎
ALTER TABLE COM ENGINE = MyISAM;

字段相关

# 修改自增
ALTER TABLE COM AUTO_INCREMENT=30;
# 删除自增 不能去掉 primary key
ALTER TABLE COM CHANGE COLUMN ID ID INT;
# 添加 primary key
ALTER TABLE COM ADD PRIMARY KEY(ID);
# 删除 primary key
ALTER TABLE COM DROP PRIMARY KEY;
# 去掉非空限制
ALTER TABLE COM CHANGE COLUMN ID ID INT NULL;
# 添加非空限制
ALTER TABLE COM CHANGE COLUMN ID ID INT NOT NULL;
# 添加字段
ALTER TABLE COM ADD COLUMN AGE TINYINT;

CHANGEMODIFY 区别: CHANGE 可以修改字段名与类型,MODIFY 只能修改类型。

索引相关

# 添加索引
ALTER TABLE COM ADD INDEX index_name (NAME);
# 添加外键索引
ALTER TABLE t2 add constraint fk foreign key (t1id) references t1(id) on delete cascade;

DML

更新排名

按照一个字段排序后,更新另一个字段为排名序号,采用临时变量。

UPDATE tb_app_user_active a,(SELECT @mycnt := 0) mycnt, 
(select id, (@mycnt := @mycnt + 1) as rank from tb_app_user_active c WHERE c.date = #{date} and c.platform = #{platform} ORDER BY amount DESC) b
SET a.global_rank = b.rank
WHERE a.id = b.id
AND a.date =  #{date}
AND a.platform = #{platform}

####

# 只插入不存在
INSERT INTO card(cardno, cardnum) SELECT '111', '100' FROM DUAL WHERE NOT EXISTS(SELECT cardno FROM card WHERE cardno = '111');
# 插入或更新  等效于 UPDATE table SET c=c+1 WHERE a=1;  
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;  

按照一个字段分组,再按照一个字段排序后,更新另一个字段为排名序号,采用临时变量。

UPDATE tb_app_user_active a, (SELECT c.id, @num:=CASE WHEN @class_one=cls_one THEN @num+1 ELSE 1 END AS rank, @class_one:=cls_one FROM tb_app_user_active c WHERE c.date =  #{date} AND c.platform = #{platform} AND c.cls_one IS NOT NULL ORDER BY c.cls_one, c.amount DESC) b
SET a.global_one = b.rank
WHERE a.id = b.id
AND a.date = #{date}
AND a.platform = #{platform}
AND a.cls_one IS NOT NULL

字符串操作

# 连接字符串, 不可用使用 ||
select CONCAT('My', 'S', 'QL');
# 正则替换 REPLACE(字段名,'表达式',‘替换后文本’)
# 转大小写 LOWER()  UPPER()
# 字符截取 SUBSTR('字符串'1(开始位置,1开始),长度);

时间操作

获取时间

# date + time
select now(); 
# sysdate() 日期时间函数跟 now() 类似,不同之处在于:now() 在执行开始时值就得到了, sysdate() 在函数执行时动态得到值
sysdate()
curdate(); 
curtime(); 
current_timestamp()
,current_timestamp
,localtime()
,localtime
,localtimestamp    -- (v4.0.6)
,localtimestamp() -- (v4.0.6) 
week(), weekofyear(), dayofweek(), weekday(), yearweek() 
set @dt = now();
select date_add(@dt, interval 1 day);        -- add 1 day
select date_add(@dt, interval 1 hour);       -- add 1 hour
select date_add(@dt, interval 1 minute);     -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);
select date_add(@dt, interval -1 day); 

时间格式化

date_format(date,'%Y-%m-%d')    
参数 含义
%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟,数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月,数值(00-12)
%p AM 或 PM
%r 时间,12-小时(hh:mm:ss AM 或 PM)
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天,与 %X 使用
%v 周 (01-53) 星期一是一周的第一天,与 %x 使用
%W 星期名
%w 周的天 (0=星期日, 6=星期六)
%X 年,其中的星期日是周的第一天,4 位,与 %V 使用
%x 年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y 年,4 位
%y 年,2 位

其他

JSON 中文乱码

  1. mysql 版本 5.7json 类型的字段有中文乱码问题, 可以提升 mysql-connector-java 到 5.1.40+ 以上解决
  2. mysql 版本 5.7.15-9-log 不支持 mysql-connector-java 到 5.1.40+, 5.7.18-14-log 支持
  3. 或者可以使用 JSON_UNQUOTE(data) as data 来解决乱码问题

模拟 Oracle Sequence

CREATE TABLE ALL_SEQ (
	name varchar(50) NOT NULL, 
	current_value bigint NOT NULL, 
	_increment int DEFAULT 1 NOT NULL, 
	PRIMARY KEY (name)
);
INSERT INTO ALL_SEQ VALUES ('DEMO_CUST_SEQ', 1, 1);
INSERT INTO ALL_SEQ VALUES ('DEMO_ORD_SEQ', 1, 1);
INSERT INTO ALL_SEQ VALUES ('DEMO_ORDER_ITEMS_SEQ', 1, 1);
INSERT INTO ALL_SEQ VALUES ('DEMO_PROD_SEQ', 1, 1);
INSERT INTO ALL_SEQ VALUES ('DEMO_USERS_SEQ', 1, 1);
INSERT INTO ALL_SEQ VALUES ('HIBERNATE_SEQUENCE', 1, 1);
INSERT INTO ALL_SEQ VALUES ('SERVER_MONITOR_SEQ', 1, 1);
INSERT INTO ALL_SEQ VALUES ('TEST_BATCH_SEQ', 1, 1);
INSERT INTO ALL_SEQ VALUES ('TEST_CLUSTER_STATE_SEQ', 1, 1);
INSERT INTO ALL_SEQ VALUES ('TEST_CONTROL_SEQ', 1, 1);
INSERT INTO ALL_SEQ VALUES ('TEST_DOMAIN_COMMENT_SEQ', 1, 1);
INSERT INTO ALL_SEQ VALUES ('TEST_DOMAIN_DEFECT_SEQ', 1, 1);
INSERT INTO ALL_SEQ VALUES ('TEST_DOMAIN_STATE_SEQ', 1, 1);
INSERT INTO ALL_SEQ VALUES ('TEST_EXECUTION_SEQ', 1, 1);
DELIMITER //
CREATE FUNCTION _nextval(n VARCHAR(50)) RETURNS BIGINT
BEGIN  
DECLARE _cur INT;  
SET _cur=(select current_value from ALL_SEQ where name= n);  
UPDATE ALL_SEQ  
SET current_value = _cur + _increment  
WHERE name=n ;  
RETURN _cur;  
END;  
// 
/*
SELECT * FROM ALL_SEQ;
SELECT _nextval("TEST_BATCH_SEQ");
*/

Search

    Table of Contents