Results 1 to 12 of 12
  1. #1

    How to alter all tables in MYSQL with only one command

    Tried and tried could not find the command
    I have this

    ALTER TABLE table_name_goes_here CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;


    but if I use it I have to do with all tables
    how can I use the above command in a way to change all tables
    in one particular database

  2. #2
    Join Date
    Jan 2006
    Location
    Athens, Greece
    Posts
    1,479
    PHP it.

    $tbls = array('t1','t2','t3','t4');
    foreach ($tbls as $t)
    {
    $query = "ALTER ".$t."CONVERT ..... ";
    $res = mysql_query($query);
    }
    You need the ALTER privilege for that.

  3. #3
    Join Date
    Aug 2003
    Location
    California, USA
    Posts
    581
    PHP Code:
    <?php
    // your connection
    mysql_connect("localhost","root","***");
    mysql_select_db("db1");

    // convert code
    $res mysql_query("SHOW TABLES");
    while (
    $row mysql_fetch_array($res))
    {
        foreach (
    $row as $key => $table)
        {
            
    mysql_query("ALTER TABLE " $table " CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
            echo 
    $key " =&gt; " $table " CONVERTED<br />";
        }
    }
    ?>
    Tested. Works. Depending on how many tables are in the database it can take a few moments. My database had 76 tables and it took about 20 seconds.
    Last edited by etogre; 03-28-2008 at 02:54 PM.

  4. #4
    thanks a lot

    what should I put here is this correct?
    Code:
    mysql_connect("localhost","DBusername","DBPassword");
    mysql_select_db("mydatabasename");
    so I make a php and run it in my browser?

  5. #5
    Join Date
    Aug 2003
    Location
    California, USA
    Posts
    581
    yep, just edit that part like you did, stick it in a PHP file and then run it.

    You will recieve output like this, and you will know it has run fine:

    0 => tablename CONVERTED
    Tables_in_dbname => tablename CONVERTED
    0 => table2 CONVERTED
    Tables_in_dbname => table2 CONVERTED
    etc..
    Last edited by etogre; 03-29-2008 at 01:20 AM.

  6. #6

    Thanks

    Just wanted to say THANKS!!!! I lost almost 10 hours trying to solve a problem with UTF8 in my MySQL base ... and you definately saved a lot of my nerves!

    Thanks again!!!

    Be fine!

  7. #7
    Join Date
    May 2009
    Posts
    766
    FYI...provided you have the PHP CLI binary...you should just be able to do this on the command line:

    # php myScript.php

  8. #8

    Question Use HeidiSQL

    You can use heidisql. it has a bulk table editor. u can alter all table at a time. heidisql is totally free.... thnx

  9. #9
    Quote Originally Posted by etogre View Post
    PHP Code:
    <?php
    // your connection
    mysql_connect("localhost","root","***");
    mysql_select_db("db1");

    // convert code
    $res mysql_query("SHOW TABLES");
    while (
    $row mysql_fetch_array($res))
    {
        foreach (
    $row as $key => $table)
        {
            
    mysql_query("ALTER TABLE " $table " CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
            echo 
    $key " =&gt; " $table " CONVERTED<br />";
        }
    }
    ?>
    Tested. Works. Depending on how many tables are in the database it can take a few moments. My database had 76 tables and it took about 20 seconds.
    My database has 251 tables, this PHP code has saved me a lot of time converting them.

    Thank you very much, Etogre!

  10. #10
    PHP 7.0 will be released in the next week or so. I suspect it will be popular with hosting companies, particularly shared hosting companies, due to its significant performance increases and decreased memory usage.

    The above ancient code will not work with PHP 7.0 because the mysql extension was removed. All ancient PHP code using the mysql extension will need to be rewritten to use the mysqli extension. You have been warned.

  11. #11
    Quote Originally Posted by DionDesigns View Post
    PHP 7.0 will be released in the next week or so. I suspect it will be popular with hosting companies, particularly shared hosting companies, due to its significant performance increases and decreased memory usage.

    The above ancient code will not work with PHP 7.0 because the mysql extension was removed. All ancient PHP code using the mysql extension will need to be rewritten to use the mysqli extension. You have been warned.
    Thanks for the heads-up, DionDesigns.

  12. #12
    Join Date
    Oct 2009
    Location
    New York
    Posts
    46
    This can be done is straight SQL using a cursor. Something like the following:

    Code:
    delimiter //
    
    drop procedure if exists convert_tables //  
    create procedure convert_tables()
    begin
    	declare done int default false;
    	declare table_to_change char(255);
    
    	declare cur1 cursor for select table_name from information_schema.tables where table_schema = 'db_name_goes_here';
    	declare continue handler for not found set done = true;
    
    	open cur1;
    
    	repeat
    	fetch cur1 into table_to_change;
    	if not done then
    		set @sql = CONCAT('alter table `db_name_goes_here`.', table_to_change, ' convert to character set utf8 collate utf8_unicode_ci');
    		prepare stmt from @sql;
    		execute stmt;
    		drop prepare stmt;
    	end if;
    	until done end repeat;
    
    	close cur1;
    end //
    
    delimiter ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •