mysql_update_column_name.sql 3.8 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192
  1. USE yisha_admin;
  2. DROP FUNCTION IF EXISTS `Fun_GetNewName`;
  3. DELIMITER $$
  4. CREATE FUNCTION Fun_GetNewName(name varchar(50)) RETURNS varchar(50) DETERMINISTIC
  5. BEGIN
  6. SET name = CONCAT(UCASE(LEFT(name, 1)),SUBSTRING(name, 2));
  7. SET name = REPLACE(name,'_a','A');
  8. SET name = REPLACE(name,'_b','B');
  9. SET name = REPLACE(name,'_c','C');
  10. SET name = REPLACE(name,'_d','D');
  11. SET name = REPLACE(name,'_e','E');
  12. SET name = REPLACE(name,'_f','F');
  13. SET name = REPLACE(name,'_g','G');
  14. SET name = REPLACE(name,'_h','H');
  15. SET name = REPLACE(name,'_i','I');
  16. SET name = REPLACE(name,'_j','J');
  17. SET name = REPLACE(name,'_k','K');
  18. SET name = REPLACE(name,'_l','L');
  19. SET name = REPLACE(name,'_m','M');
  20. SET name = REPLACE(name,'_n','N');
  21. SET name = REPLACE(name,'_o','O');
  22. SET name = REPLACE(name,'_p','P');
  23. SET name = REPLACE(name,'_q','Q');
  24. SET name = REPLACE(name,'_r','R');
  25. SET name = REPLACE(name,'_s','S');
  26. SET name = REPLACE(name,'_t','T');
  27. SET name = REPLACE(name,'_u','U');
  28. SET name = REPLACE(name,'_v','V');
  29. SET name = REPLACE(name,'_w','W');
  30. SET name = REPLACE(name,'_x','X');
  31. SET name = REPLACE(name,'_y','Y');
  32. SET name = REPLACE(name,'_z','Z');
  33. RETURN name;
  34. END
  35. $$
  36. DROP PROCEDURE IF EXISTS `SP_UpdateColumnName`;
  37. DELIMITER $$
  38. CREATE PROCEDURE `SP_UpdateColumnName`()
  39. BEGIN
  40. DECLARE tableName VARCHAR(50);
  41. DECLARE fieldName VARCHAR(50);
  42. DECLARE fieldType VARCHAR(50);
  43. DECLARE tableDone BOOLEAN DEFAULT false;
  44. DECLARE fieldDone BOOLEAN DEFAULT false;
  45. DECLARE curTable CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND
  46. (table_type='base table' or table_type='BASE TABLE') AND
  47. table_name in('sys_area','sys_auto_job','sys_auto_job_log','sys_data_dict','sys_data_dict_detail','sys_department',
  48. 'sys_log_api','sys_log_login','sys_log_operate','sys_menu','sys_menu_authorize','sys_news',
  49. 'sys_position','sys_role','sys_user','sys_user_belong');
  50. DECLARE CONTINUE HANDLER FOR NOT FOUND SET tableDone = TRUE;
  51. OPEN curTable;
  52. curTableLoop: LOOP
  53. FETCH curTable INTO tableName;
  54. IF tableDone THEN
  55. LEAVE curTableLoop;
  56. END IF;
  57. BEGIN
  58. DECLARE curField CURSOR FOR SELECT column_name,column_type FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = tableName;
  59. DECLARE CONTINUE HANDLER FOR NOT FOUND SET fieldDone = TRUE;
  60. OPEN curField;
  61. curFieldLoop: LOOP
  62. FETCH curField INTO fieldName,fieldType;
  63. IF fieldDone THEN
  64. LEAVE curFieldLoop;
  65. END IF;
  66. BEGIN
  67. SET @newFieldName = Fun_GetNewName(fieldName);
  68. SET @tempSql = CONCAT('ALTER TABLE ', tableName ,' CHANGE COLUMN ',fieldName,' ',@newFieldName ,' ',fieldType);
  69. SELECT @tempSql;
  70. PREPARE stmt FROM @tempSql;
  71. EXECUTE stmt;
  72. DEALLOCATE PREPARE stmt;
  73. END;
  74. END LOOP curFieldLoop;
  75. CLOSE curField;
  76. SET @newTableName = Fun_GetNewName(tableName);
  77. SET @tempSql = CONCAT('RENAME TABLE ', tableName ,' TO ',@newTableName);
  78. SELECT @tempSql;
  79. PREPARE stmt FROM @tempSql;
  80. EXECUTE stmt;
  81. DEALLOCATE PREPARE stmt;
  82. SELECT tableName;
  83. SET fieldDone = FALSE;
  84. END;
  85. END LOOP curTableLoop;
  86. CLOSE curTable;
  87. END;
  88. $$
  89. call SP_UpdateColumnName();
  90. DROP FUNCTION IF EXISTS `Fun_GetNewName`;
  91. DROP PROCEDURE IF EXISTS `SP_UpdateColumnName`;