title: MySQL 小知識點總結(jié)
date: 2016-01-09
update time: 2016-05-14
comments: true
category: DB
tags: MySQL, outfile, infile
查詢數(shù)據(jù)庫的大形裳 （總大小，數(shù)據(jù)大小，索引大星钤辍）
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' ;
數(shù)據(jù)庫導出數(shù)據(jù)到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 '' ....
數(shù)據(jù)庫從csv文件導入數(shù)據(jù)
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 隨機時間
一段時間范圍內(nèi)的隨機時間, 結(jié)束時間可以為 `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)
