mysql存储过程日期类型_mysql中多种日期类型的格式话---用存储过程实现

其中三个参数:数据库名,表名,列名

temp_d_name:数据库名

temp_t_name:表名

temp_c_name:列名

CREATE DEFINER=`root`@`localhost` PROCEDURE `date_format`(temp_d_name VARCHAR(255),temp_t_name VARCHAR(255),temp_c_name VARCHAR(255))

BEGIN

SET @temp_d_name=temp_d_name;#数据库名

SET @temp_t_name=temp_t_name;#表名

SET @temp_c_name = temp_c_name;#列名

SET @temp_a_name=CONCAT(temp_t_name,'_format');#新建的表名

#生成一个新表

SET @sql = CONCAT('CREATE TABLE ',@temp_d_name,'.',@temp_a_name,' like ',@temp_d_name,'.',@temp_t_name);

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

#将所有数据放入新表

SET @SQL = CONCAT('insert into ',@temp_d_name,'.',@temp_a_name,' select * from ',@temp_d_name,'.',@temp_t_name);

PREPARE stmt1 FROM @SQL;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

#格式化结果

SET @SQL = CONCAT('update ',@temp_d_name,'.',@temp_a_name,' set ',@temp_c_name," =STR_TO_DATE(REPLACE(",@temp_c_name,",' ',''),'%d %M %Y @') where ",@temp_c_name ," REGEXP'^[0-9]{1,2}.*[July|Janurary|February|May|June|July|August|September|October|November|December|March].*@.*[0-9]$'");

PREPARE stmt1 FROM @SQL;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

SET @SQL = CONCAT('update ',@temp_d_name,'.',@temp_a_name,' set ',@temp_c_name," =STR_TO_DATE(REPLACE(",@temp_c_name,",' ',''),'%Y-%m-%d') where ",@temp_c_name ," REGEXP'^[0-3][0-9]{3}-[0-9]{1,2}-.*' ");

PREPARE stmt1 FROM @SQL;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

SET @SQL = CONCAT('update ',@temp_d_name,'.',@temp_a_name,' set ',@temp_c_name," =STR_TO_DATE(REPLACE(",@temp_c_name,",' ',''),'%Y年%m月%d日') where ",@temp_c_name ," REGEXP'^[0-9]{4}年[0-9]{1,2}月[0-9]{1,2}[日0-9]'");

PREPARE stmt1 FROM @SQL;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

SET @SQL = CONCAT('update ',@temp_d_name,'.',@temp_a_name,' set ',@temp_c_name," =STR_TO_DATE(REPLACE(",@temp_c_name,",' ',''),'%d/%m/%Y') where ",@temp_c_name ," REGEXP'^[[:digit:]]{1,2}/[[:digit:]]{1,2}/[[:digit:]]{4}$'");

PREPARE stmt1 FROM @SQL;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

SET @SQL = CONCAT('update ',@temp_d_name,'.',@temp_a_name,' set ',@temp_c_name," =STR_TO_DATE(REPLACE(",@temp_c_name,",' ',''),'%Y%m%d') where ",@temp_c_name ," REGEXP'^[[:digit:]]{4}/[[:digit:]][0-9]?/.*[0-9]$'");

PREPARE stmt1 FROM @SQL;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

SET @SQL = CONCAT('update ',@temp_d_name,'.',@temp_a_name,' set ',@temp_c_name," =STR_TO_DATE(REPLACE(",@temp_c_name,",' ',''),'%M%d%y') where ",@temp_c_name ," REGEXP'^[July|Janurary|February|May|June|July|August|September|October|November|December|March].+[0-9]{1,2},.+[0-9]{4}$'");

PREPARE stmt1 FROM @SQL;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

SET @SQL = CONCAT('update ',@temp_d_name,'.',@temp_a_name,' set ',@temp_c_name," =STR_TO_DATE(REPLACE(",@temp_c_name,",' ',''),'%Y-%m-%d') where ",@temp_c_name ," REGEXP'^[0-9]{2}-[0-9]{1,2}-[0-9]{1,2}$'");

PREPARE stmt1 FROM @SQL;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

-- #查询没有完成格式化的数据

-- select * from temp where release_date not REGEXP '^[0-3][0-9]{3}-[0-9]{1,2}-.*[0-9]$';

#查询新表结果

SET @SQL=CONCAT('SELECT * FROM ',@temp_d_name,'.',@temp_a_name);

--

PREPARE stmt1 FROM @SQL;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

END

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值