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