mysql中使用sql命令將時間戳解析成datetime型別存入

2022-11-23 14:01:37

實現思路

需求

需要將本資料庫的資料進行處理（新增字首），然後匯入主資料庫。

但是當前資料庫記錄的create_time、update_time 是bigint 型別，存放的是時間戳。eg.1646124455

而主資料庫的 create_time、update_time 是 datetime 型別的欄位，所以需要將時間戳解析成時間並存放到對應位置。

給所有的表新增字首
給所有的表新增欄位，用於儲存解析後的時間即 datetime 型別
解析時間戳欄位，將解析後的時間存到對應的欄位中
刪除時間戳的欄位
將第二步新增的欄位的名稱改成create_time、update_time

一、修改庫中所有表名，新增字首

1.sql更改表名

rename table test to test1;

2.sql一次更改多個表名

rename table `name` to name1 , tel to tel1;

3.sql生成批次執行的語句

select concat('rename table ',table_name,'  to hts_',table_name,';') 
from information_schema.tables 
where TABLE_SCHEMA ="demo";

4.執行批次生成的所有語句

二、給庫中所有的表新增欄位

1.sql給表新增欄位

alter table hts_name add column create_time int;

2.sql一次給表中新增多個欄位

alter table hts_user_profile 
add column (create_time_date datetime , update_time_date datetime);

3.sql生成批次執行的語句

select concat('alter table ',table_name,' add column (create_time_date datetime , update_time_date datetime);') from information_schema.tables 
where table_name like'hts_%'
and TABLE_SCHEMA ="hts";

三、將時間戳解析並賦值到新的欄位

1.sql將表中a欄位的值解析後賦值給b欄位

update hts_user_profile 
set create_time_date = FROM_UNIXTIME(create_time,'%Y-%m-%d %H:%i:%s');
update hts_user_profile 
set update_time_date = FROM_UNIXTIME(update_time,'%Y-%m-%d %H:%i:%s');

2.sql一次更新多個欄位的資料

update hts_user_profile set 
create_time_date = FROM_UNIXTIME(create_time,'%Y-%m-%d %H:%i:%s'),
update_time_date = FROM_UNIXTIME(update_time,'%Y-%m-%d %H:%i:%s');

3.sql生成批次執行的語句

select concat('update ',table_name,' set create_time_date = FROM_UNIXTIME(create_time,"%Y-%m-%d %H:%i:%s");') 
from information_schema.tables where table_name like'hts_%';
select concat('update ',table_name,' set update_time_date = FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s");') 
from information_schema.tables where table_name like'hts_%';

四、刪除庫中所有表的某個欄位

1.sql將表的某個欄位刪除

alter table hts_user_profile drop column create_time;

2.sql生成批次執行的語句

select concat('alter table ',table_name,' drop column create_time;') 
from information_schema.tables where table_name like'hts_%';

五、修改庫中所有表的某個欄位名稱

1.sql修改表中的某個欄位名稱

ALTER TABLE hts_user_profile change create_time_date create_time datetime;

2.sql一次修改表的多個欄位名稱

ALTER TABLE hts_user_profile 
CHANGE create_time_date create_time datetime,
CHANGE update_time_date update_time datetime;

3.sql生成批次執行的語句

select concat('alter table ',table_name,' CHANGE create_time_date create_time datetime,CHANGE update_time_date update_time datetime;') 
from information_schema.tables where table_name like'hts_%';

彙總

/*
前提：專案庫存在本地mysql，從庫（需要資料遷移的庫）拷貝到本地資料庫；

1.修改所有的從庫表名，新增需要的字首。
2.給所有的從庫表新增欄位：create_time_date,update_time_date
3.將從庫所有的表讀取一遍，將時間戳轉成時間然後存在新欄位中
4.刪除從表的create_time  和   update_time  欄位
5.修改所有的create_time_date,update_time_date 欄位名為 create_time  和   update_time
6.同步資料（可在Navicat執行）
*/

-- 1.修改所有的從庫表名，新增需要的字首。
select concat('alter table ',table_name,' rename to ',table_name) from information_schema.tables where table_name like'dmsck_%';

-- 2.給所有的從庫表新增欄位：create_time_date,update_time_date
alter table hts_user_profile add column (create_time_date datetime , update_time_date datetime);

alter table hts_user_profile add column create_time int;

select concat('alter table ',table_name,' add column (create_time_date datetime , update_time_date datetime);') from information_schema.tables 
where table_name like'hts_%';

-- 3.將從庫所有的表讀取一遍，將時間戳轉成時間然後存在新欄位中

update hts_user_profile set create_time_date = FROM_UNIXTIME(create_time,'%Y-%m-%d %H:%i:%s');
update hts_user_profile set update_time_date = FROM_UNIXTIME(update_time,'%Y-%m-%d %H:%i:%s');

SELECT * FROM hts_user_profile WHERE create_time != update_time;

select concat('update ',table_name,' set create_time_date = FROM_UNIXTIME(create_time,"%Y-%m-%d %H:%i:%s");') from information_schema.tables 
where table_name like'hts_%';
select concat('update ',table_name,' set update_time_date = FROM_UNIXTIME(update_time,"%Y-%m-%d %H:%i:%s");') from information_schema.tables 
where table_name like'hts_%';

-- 4.刪除從表的create_time  和   update_time  欄位
alter table hts_user_profile drop column create_time;
alter table hts_user_profile drop column update_time;

select concat('alter table ',table_name,' drop column create_time;') from information_schema.tables 
where table_name like'hts_%';
select concat('alter table ',table_name,' drop column update_time;') from information_schema.tables 
where table_name like'hts_%';

-- 5.修改所有的create_time_date,update_time_date 欄位名為 create_time  和   update_time

ALTER TABLE hts_user_profile CHANGE create_time_date create_time datetime,CHANGE update_time_date update_time datetime;

select concat('alter table ',table_name,' CHANGE create_time_date create_time datetime,CHANGE update_time_date update_time datetime;') from information_schema.tables 
where table_name like'hts_%';

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

mysql中使用sql命令將時間戳解析成datetime型別存入

目錄

實現思路

需求

一、修改庫中所有表名，新增字首

二、給庫中所有的表新增欄位

三、將時間戳解析並賦值到新的欄位

四、刪除庫中所有表的某個欄位

五、修改庫中所有表的某個欄位名稱

熱門文章