Tag Archives: Mysql

What collation/encoding is my database using?

Very simple.

USE `your_database`
show variables like "character_set_database";
show variables like "collation_database";

Convert latin1 tables/fields to utf8

I had some screwup which made all my tables/fields latin1. That sucks, because I didn’t want to manually correct this. I found the following lines of code that fix this problem.

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

Also note that if you’re converting from some other obscure character set it’s best to first convert to binary and then convert to utf8, making sure you won’t lose data in the conversion.

Delete all tables in a MySQL database

I was looking for a script to delete all tables in a MySQL database without having to drop the database.

mysqldump -u [username] -p --add-drop-table [database] | grep ^DROP | mysql -u [username] -p

/cc @davelens

MySQL configuration file

Place the configuration file ‘my.cnf’ in your home directory.

I no longer need to type ‘mysql -uroot -proot …’ to connect to my local databases. You can also set some other options such as the default character set which defaulted to latin1 in my case. I don’t like latin1, but I also don’t like typing this over and over when connection to a database.

Let mysqldump help you!

I used to dump mysql data using PHPMyAdmin. People should get kicked in the nuts for doing that.

Dumping a (remote) database:

mysqldump -uroot -proot –host=mysql.openminds.be my_database > Desktop/dump.sql

Restoring a database:

mysql -uroot -proot my_database < Desktop/dump.sql

Disclaimer: I know that it’s stupid to set the password in the command, but this is just for the example. /cc @adeepersilence

MySQL command line import

I always seem to forget the right command.

mysql -uroot -proot databaseName < /location/to/my/file.sql

MySQL connections in Zend Studio 8.0

Recently I was having problems to get my unit tests to connect to my local MySQL server when ran within Zend Studio 8. After some reading on the internet it seems, that this issue is caused by the MySQL server running on the default port (3306). I’ve changed this to 8889 as MAMP (on mac) suggests and now it works like a charm.

Strange mysql sorting behaviour with enums

If you have an ENUM(‘Y’,'N’) field and you want to sort ASC on this field, mysql will show the Y values before the N values. It seems this is based on the order you defined them when creating your table… See the screenshot.

I’d like to add the explanation from mysql.com which can be found here.

ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.) For example, ‘a’  sorts before ‘b’ for ENUM(‘a’, ‘b’), but ‘b’ sorts before ‘a’ for ENUM(‘b’, ‘a’). The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values. To prevent unexpected results, specify the ENUM list in alphabetic order.

Setting your timezone in mysql

If you want to set your timezone in mysql for your database connection, do this.

SET time_zone = ‘+2:00′;

Keep in mind that the number should be correct and can also have a minus sign, based on the timezone you want to set ofcourse. Do watch out for the UTC timezone, because mysql requires the +.

SET time_zone = ‘+0:00′;