mysql中使用sql命令將時(shí)間戳解析成datetime類型存入

更新時(shí)間：2022年11月22日 10:06:02 作者：夢若辰宇

這篇文章主要介紹了mysql中使用sql命令將時(shí)間戳解析成datetime類型存入，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

實(shí)現(xiàn)思路

需求

需要將本數(shù)據(jù)庫的數(shù)據(jù)進(jìn)行處理（添加前綴），然后導(dǎo)入主數(shù)據(jù)庫。

但是當(dāng)前數(shù)據(jù)庫記錄的create_time、update_time 是bigint 類型，存放的是時(shí)間戳。eg.1646124455

而主數(shù)據(jù)庫的 create_time、update_time 是 datetime 類型的字段，所以需要將時(shí)間戳解析成時(shí)間并存放到對應(yīng)位置。

給所有的表添加前綴
給所有的表新增字段，用于存儲解析后的時(shí)間即 datetime 類型
解析時(shí)間戳字段，將解析后的時(shí)間存到對應(yīng)的字段中
刪除時(shí)間戳的字段
將第二步新增的字段的名稱改成create_time、update_time

一、修改庫中所有表名，添加前綴

1.sql更改表名

rename table test to test1;

2.sql一次更改多個表名

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

3.sql生成批量執(zhí)行的語句

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

4.執(zhí)行批量生成的所有語句

在這里插入圖片描述

二、給庫中所有的表添加字段

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生成批量執(zhí)行的語句

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";

三、將時(shí)間戳解析并賦值到新的字段

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一次更新多個字段的數(shù)據(jù)

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生成批量執(zhí)行的語句

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生成批量執(zhí)行的語句

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生成批量執(zhí)行的語句

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_%';

匯總

/*
前提：項(xiàng)目庫存在本地mysql，從庫（需要數(shù)據(jù)遷移的庫）拷貝到本地?cái)?shù)據(jù)庫；

1.修改所有的從庫表名，添加需要的前綴。
2.給所有的從庫表添加字段：create_time_date,update_time_date
3.將從庫所有的表讀取一遍，將時(shí)間戳轉(zhuǎn)成時(shí)間然后存在新字段中
4.刪除從表的create_time  和   update_time  字段
5.修改所有的create_time_date,update_time_date 字段名為 create_time  和   update_time
6.同步數(shù)據(jù)（可在Navicat執(zhí)行）
*/

-- 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.將從庫所有的表讀取一遍，將時(shí)間戳轉(zhuǎn)成時(shí)間然后存在新字段中

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_%';

以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: