A script to change all tables and fields to the utf-8-bin collation in MYSQL

php sql mysql utf-8 collation
user19302 picture user19302 · Sep 19, 2008 · Viewed 72.8k times · Source

Is there a SQL or PHP script that I can run that will change the default collation in all tables and fields in a database?

I can write one myself, but I think that this should be something that readily available at a site like this. If I can come up with one myself before somebody posts one, I will post it myself.

Answer

DavidWinterbottom picture DavidWinterbottom · Mar 21, 2009

Can be done in a single command (rather than 148 of PHP):

mysql --database=dbname -B -N -e "SHOW TABLES" \
| awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' \
| mysql --database=dbname &

You've got to love the commandline... (You might need to employ the --user and --password options for mysql).

EDIT: to avoid foreign key problems, added SET foreign_key_checks = 0; and SET foreign_key_checks = 1;

Related questions

How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL query String contains

I've been trying to figure out how I can make a query with MySQL that checks if the value (string $haystack ) in a certain column contains certain data (string $needle), like this: mysql_query(" SELECT * FROM `table` WHERE `column`.contains(…

Read More
How to 'insert if not exists' in MySQL?

I started by googling, and found this article which talks about mutex tables. I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I …

Read More