Results 1 to 8 of 8
  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

Related Posts from theWHIR.com

Posting Permissions

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