mysql中使用sql命令將時間戳解析成datetime類型存入
更新時間：2022年11月22日 10:06:02 作者：夢若辰宇
這篇文章主要介紹了mysql中使用sql命令將時間戳解析成datetime類型存入，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
實現思路
需求
需要將本數據庫的數據進行處理（添加前綴），然后導入主數據庫。
但是當前數據庫記錄的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生成批量執(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";三、將時間戳解析并賦值到新的字段
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生成批量執(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_%';匯總
/*
前提：項目庫存在本地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執(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.將從庫所有的表讀取一遍，將時間戳轉成時間然后存在新字段中
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_%';以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
這篇文章主要介紹了MySql中的longtext字段的返回問題及解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-07-07
如果要在程序運行過程中操作數據庫中的數據,那得先學會使用SQL語句,下面這篇文章主要給大家介紹了關于SQL查詢語句更新和刪除數據的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2023-01-01
mysql5.7.18.zip免安裝版本配置教程（windows）
這篇文章主要為大家詳細介紹了mysql5.7.18.zip安裝教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-05-05
請問，什么是最好的參考文檔，我的答案是：真實可行的樣例語句。2010-12-12
這篇文章主要為大家介紹了MySQL?InnoDB?存儲引擎的底層邏輯架構詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-09-09
這篇文章主要介紹了MySQL8.0服務無法正常啟動的解決過程，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-12-12
MySQL中你可以使用SELECT…INTO OUTFILE語句來簡單的導出數據到文本文件上,這篇文章給大家介紹了Mysql 中如何導出數據,感興趣的朋友跟隨小編一起看看吧2023-11-11
Linux系統(tǒng)下自行編譯安裝MySQL及基礎配置全過程解析
這篇文章主要介紹了Linux系統(tǒng)下自行編譯安裝MySQL及基礎配置全過程解析,配置方面主要針對InnoDB引擎來講,需要的朋友可以參考下2016-02-02
下面這個語句是鎖定一行數據，開始讀取，一直到刪除后都不會有第二個人也讀到這條數據2011-09-09
mysql要同步原始全量數據，也要實時同步MySQL特定庫的特定表增量數據，同時對應的修改、刪除也要對應,下面就為大家分享一下2021-11-11