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