Thursday, May 20, 2010

Script to rename multiple table in MySQL

Baru-baru ni aku faced problem untuk rename table prefix secara pukal...kat database tu ada 186 tables...
kalau nak tukar satu-satu...pergghh...memang lebam...setelah google punya google...aku jumpa solution ni...tapi kena pakai ruby script dan tukar ke sql statement..tapi kalau korang nak pakai convert ke php pun x de hal...guna concept yg sama jek...

Ikut ni :


  1. masuk mysql...pastu show tables;
  2. copy semua tables yg korang nak rename tu...
  3. paste kat dlm script ni (dlm cth ni aku bagi nama file ni batch_rename.sh ) :

    OLD_PREFIX = "mdl"
    NEW_PREFIX = "mdlsirap"
    data=<<-EOD | mdl_assignment | | mdl_assignment_submissions | | mdl_backup_config | | mdl_backup_courses | | mdl_backup_files | | mdl_backup_ids | | mdl_backup_log | | mdl_block | | mdl_block_instance |

    | mdl_block_pinned |

    EOD
    tables = []
    data.each_line do |line|
    if line=~/(#{OLD_PREFIX}\w+)/
    puts "RENAME TABLE #{$1} TO #{NEW_PREFIX}#{$1};"
    end
    end


  4. Pastu runkan script ni pakai ruby punya command-line (aku assume korang dah install ruby) :
    #ruby batch_rename.sh
  5. Dia akan kluar output :
    RENAME TABLE mdl_assignment TO mdlsirap_assignment;
    RENAME TABLE mdl_assignment_submissions TO mdlsirap_assignment_submissions;
    RENAME TABLE mdl_backup_config TO mdlsirap_backup_config;
    RENAME TABLE mdl_backup_courses TO mdlsirap_backup_courses;
    RENAME TABLE mdl_backup_files TO mdlsirap_backup_files;
    RENAME TABLE mdl_backup_ids TO mdlsirap_backup_ids;
    RENAME TABLE mdl_backup_log TO mdlsirap_backup_log;
    RENAME TABLE mdl_block TO mdlsirap_block;
    RENAME TABLE mdl_block_instance TO mdlsirap_block_instance;
    RENAME TABLE mdl_block_pinned TO mdlsirap_block_pinned;
  6. just masukan sql statement ni dlm satu file lain...tapi kalau nak senang...buat step no. 4 tu camni :
    #ruby batch_rename.sh > a.sql
  7. Runkan sql file tu guna mysql...:
    #mysql namadatabase <>
  8. Selesai....
reference :
http://zerokspot.com/weblog/e/676/

No comments:

Post a Comment