title: MySQL 小知识点总结
date: 2016-01-09
update time: 2016-05-14
comments: true
category: DB
tags: MySQL, outfile, infile
查询数据库的大小（总大小，数据大小，索引大小）
SELECT ROUND(SUM(data_length+index_length)/1024/1024) AS total_bm,
ROUND(SUM(data_length)/1024/1024) as data_mb,
ROUND(SUM(index_length)/1024/1024) as index_mb
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA='peanut_userspace_1' AND TABLE_NAME = 'USER_PAIR_ROUND' ;
数据库导出数据到csv文件 (注意 \r\n 和 \n)
SELECT * INTO OUTFILE '/tmp/user_info-table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM USER_INFO;
# 注意：
这里可以查询具体的字段，导出到csv文件，就像：
select id, sex, name into outfile '' ....
数据库从csv文件导入数据
LOAD DATA INFILE '/tmp/test.csv' INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
# 注意：
这里可以导入具体的字段到表，就像：
load data infile '' into table table-name .... ignore 1 lines (id, sex, name);
MySQL 随机时间
一段时间范围内的随机时间, 结束时间可以为 `now()`
DATE_ADD('2016-05-08 06:00:00',INTERVAL FLOOR(1+RAND()*((ABS(UNIX_TIMESTAMP('2016-05-10 20:00:00')-UNIX_TIMESTAMP('2016-05-08 06:00:00')))-1)) SECOND)
DATE_ADD('2016-05-08 06:00:00',INTERVAL FLOOR(1+RAND()*((ABS(UNIX_TIMESTAMP(now()')-UNIX_TIMESTAMP('2016-05-08 06:00:00')))-1)) SECOND)
