Results 1 to 12 of 12
Hybrid View
-
03-28-2008, 12:16 PM #1WHT 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
-
03-28-2008, 01:30 PM #2Web Hosting Master
- Join Date
- Jan 2006
- Location
- Athens, Greece
- Posts
- 1,481
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.
-
03-28-2008, 02:40 PM #3Web Hosting Master
- Join Date
- Aug 2003
- Location
- California, USA
- Posts
- 582
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 . " => " . $table . " CONVERTED<br />";
}
}
?>Last edited by etogre; 03-28-2008 at 02:54 PM.
-
03-29-2008, 01:04 AM #4WHT 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");
-
11-18-2015, 03:11 AM #5New Member
- Join Date
- Oct 2015
- Posts
- 2
-
03-29-2008, 01:15 AM #6Web Hosting Master
- Join Date
- Aug 2003
- Location
- California, USA
- Posts
- 582
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.
-
11-19-2009, 08:03 AM #7New 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!
-
11-19-2009, 12:22 PM #8Web 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
-
04-04-2013, 07:06 AM #9New Member
- Join Date
- Apr 2013
- Posts
- 1
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
-
11-18-2015, 10:15 AM #10WHT Addict
- Join Date
- Nov 2015
- Location
- Uncertain due to momentum
- Posts
- 125
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-18-2015, 07:27 PM #11New Member
- Join Date
- Oct 2015
- Posts
- 2
-
11-19-2015, 11:23 AM #12Junior Guru Wannabe
- 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 ;