Collation: Difference between revisions
→Changing Character Sets and Collations: update config example; remove duplicate |
No edit summary |
||
| Line 41: | Line 41: | ||
wiki_freephile, 1, utf8_general_ci | wiki_freephile, 1, utf8_general_ci | ||
</pre> | </pre> | ||
So, | |||
Note that this query is counting the '''columns''' which have an undesirable collation. (Recall that encoding/collation can be set at the server, database, table, and column levels.) | |||
So, start off by setting the defaults for each database: | |||
<source lang="mysql"> | <source lang="mysql"> | ||
ALTER DATABASE wiki_freephile CHARACTER SET = 'binary' COLLATE = 'BINARY'; | ALTER DATABASE wiki_freephile CHARACTER SET = 'binary' COLLATE = 'BINARY'; | ||
| Line 48: | Line 52: | ||
</source> | </source> | ||
You can check the status of each table too: | You can check the status of each '''table''' too: | ||
<source lang="mysql"> | <source lang="mysql"> | ||
SET @DATABASE_NAME = 'wiki_freephile'; | SET @DATABASE_NAME = 'wiki_freephile'; | ||
| Line 56: | Line 60: | ||
AND TABLE_COLLATION not in ('binary'); | AND TABLE_COLLATION not in ('binary'); | ||
</source> | </source> | ||
Then, down in the section below on changing engines, there's an example SQL query for generating '''<code>ALTER TABLE</code>''' statements to fix the table defaults<ref>https://mariadb.com/kb/en/setting-character-sets-and-collations/#table-level</ref>. These statements would be in the form: | |||
<source lang="mysql"> | |||
ALTER TABLE table_name | |||
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]; | |||
</source> | |||
In my case, there are 90-100 columns that have old latin1 character sets. | |||
<source lang="mysql"> | |||
SET @DATABASE_NAME = 'wiki_greg'; | |||
SELECT | |||
TABLE_SCHEMA, | |||
TABLE_NAME, | |||
COLUMN_NAME, | |||
CHARACTER_SET_NAME, | |||
COLLATION_NAME | |||
FROM | |||
information_schema.columns AS cols | |||
WHERE | |||
table_schema = @DATABASE_NAME | |||
AND COLLATION_NAME NOT IN ('binary'); | |||
</source> | |||
We can use the information schema to list them all out. And you can also inspect each TABLE, with e.g. <code>SHOW CREATE TABLE brokenlinks \G</code> | |||
To set the mysql server default character set to binary <ref>https://mariadb.com/kb/en/library/setting-character-sets-and-collations/ https://scottlinux.com/2017/03/04/mysql-mariadb-set-character-set-and-collation-to-utf8/</ref>, add the following to <code>/etc/mysql/my.cnf</code> (Debian/Ubuntu) or <code>/etc/my.cnf</code> (Fedora/Centos/RHEL) under the '''<nowiki>[mysqld]</nowiki>''' section: | To set the mysql server default character set to binary <ref>https://mariadb.com/kb/en/library/setting-character-sets-and-collations/ https://scottlinux.com/2017/03/04/mysql-mariadb-set-character-set-and-collation-to-utf8/</ref>, add the following to <code>/etc/mysql/my.cnf</code> (Debian/Ubuntu) or <code>/etc/my.cnf</code> (Fedora/Centos/RHEL) under the '''<nowiki>[mysqld]</nowiki>''' section: | ||
| Line 184: | Line 213: | ||
*If you ever need to put the DB data into something Unicode, then be sure to use the utf8mb4 (Multi-byte 4) variety. | *If you ever need to put the DB data into something Unicode, then be sure to use the utf8mb4 (Multi-byte 4) variety. | ||
The | The '''recommended''' charset/collation is 'binary BINARY'. | ||
<source lang="mysql"> | <source lang="mysql"> | ||
ALTER table <tablename> CHARACTER SET = 'binary' COLLATE = 'BINARY'; | ALTER table <tablename> CHARACTER SET = 'binary' COLLATE = 'BINARY'; | ||
| Line 239: | Line 268: | ||
WHERE table_schema = "wiki_demo"; | WHERE table_schema = "wiki_demo"; | ||
</source> | </source> | ||
<references /> | |||
[[Category:MediaWiki]] | [[Category:MediaWiki]] | ||
| Line 246: | Line 283: | ||
[[Category:QualityBox]] | [[Category:QualityBox]] | ||
[[Category:Wiki]] | [[Category:Wiki]] | ||