Jump to Navigation

Things I did not know about MySQL

Did you know that MySQL can join at most 61 tables in one query?

Did you know that table data is by default stored as latin1 with the  latin1_swedish_ci collation?

Things I did not know but should have - as I encountered both issues the same day.

The 61 tables thing seems to be hard-coded and can not be changed. That can be a bother if you use machine-generated SQL (as I did). It can probably be worked around by using subqueries, but I have not tested as I could solve the problem by limiting the number of tables in the query.

The table data storage choice seems unfortunate this day and age. It is, of course, possible to specify you want Unicode and to convert your existing database, but it is a bit of work. To change the default, edit your /etc/mysql/my.cnf and add under the [mysqld] heading the lines:

collation-server=utf8_unicode_ci
character-set-server=utf8

Restart mysqld to apply this change.

To modify existing tables you can use:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

To see the current character set used try this query (add appropriate WHERE-clauses):

SELECT table_schema,table_name,column_name, character_set_name,collation_name
FROM  information_schema.columns

All in all an instructive afternoon.

Tags:


Blog_article | by Dr. Radut