hosted by liquidweb


Go Back   Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : How to alter all tables in MYSQL with only one command
Reply

Forum Jump

How to alter all tables in MYSQL with only one command

Reply Post New Thread In Programming Discussion Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old
WHT Addict
 
Join Date: Oct 2007
Posts: 129

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



Sponsored Links
  #2  
Old
Community Guide
 
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  
Old
Web Hosting Master
 
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.
Sponsored Links
  #4  
Old
WHT Addict
 
Join Date: Oct 2007
Posts: 129
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  
Old
Web Hosting Master
 
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:

Quote:
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  
Old
New Member
 
Join Date: Nov 2009
Posts: 1
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  
Old
Web Hosting Master
 
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  
Old
New Member
 
Join Date: Apr 2013
Posts: 1
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

Reply

Related posts from TheWhir.com
Title Type Date Posted
Tesora's OpenStack DBaaS Supports MongoDB, Cassandra, Redis, and MySQL Web Hosting News 2014-05-23 14:53:11
Google Releases Hosted Database Service Cloud SQL to General Availability Web Hosting News 2014-02-12 13:46:02
Linode Releases CLI Tool for Cloud Platform Web Hosting News 2014-01-29 17:15:43
Google Cloud Provides Support For Native MySQL Connections Web Hosting News 2013-11-01 14:36:06
CIO Summit Chicago 2013 Web Hosting Events 2013-08-09 15:39:54


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
WHT Host Brief Email:

We respect your privacy. We will never sell, rent, or give away your address to any outside party, ever.

Advertisement:
Web Hosting News:
WHT Membership
WHT Membership



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?