mysql相关操作笔记

一、数据迁移

（1）从源库10.x.x.x的表A的数据复制到目标库192.x.x.x的表B

-- 把源库的数据导出到本地磁盘
select * from table_A into outfile "/tmp/tmp.sql"

-- 目标库导入数据
load data infile "/tmp/tmp.sql" into table table_B;

-- 帮助文档
-- help select
-- help load data

 

 

二、日期相减

注意：直接使用now() - one_time得到的数字特别奇怪，不知道单位是什么。

-- 获取时间差，得到日期
select now(), begin_ts, timediff(now(), begin_ts) as duration from xx limit 1;
+---------------------+---------------------+----------+
| now()               | begin_ts            | duration |
+---------------------+---------------------+----------+
| 2017-03-10 17:26:10 | 2017-03-09 14:58:32 | 26:27:38 |
+---------------------+---------------------+----------+
1 row in set (0.00 sec)


-- 使用time_to_sec转换为秒
select now(), begin_ts, time_to_sec(timediff(now(), begin_ts)) as duration from x limit 1;
+---------------------+---------------------+----------+
| now()               | begin_ts            | duration |
+---------------------+---------------------+----------+
| 2017-03-10 17:27:04 | 2017-03-09 14:58:32 |    95312 |
+---------------------+---------------------+----------+
1 row in set (0.01 sec)



-- 方法二，直接转换成时间戳相减
select now(), begin_ts, unix_timestamp(now()) - unix_timestamp(begin_ts) as duration from x limit 1;                         
+---------------------+---------------------+----------+
| now()               | begin_ts            | duration |
+---------------------+---------------------+----------+
| 2017-03-10 17:28:33 | 2017-03-09 14:58:32 |    95401 |
+---------------------+---------------------+----------+
1 row in set (0.00 sec)

 

转载于:https://my.oschina.net/holton/blog/856341