其中三个参数:数据库名,表名,列名
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