Merkistö ja collation MySQL-tietokannassa

Charset (tegnsæt) määrää, mitä merkkejä tietokanta voi tallentaa, ja collation määrittää, miten merkit verrataan ja lajitellaan. Väärä charset on yleisin syy siihen, että tanskankieliset kirjaimet (æ, ø, å) tai emojit näkyvät kysymysmerkkeinä tai mojibakeina (esim. æ i stedet for æ).

¶Suositellut arvot

Käytä utf8mb4 charsetina ja utf8mb4_unicode_ci collationina. utf8mb4 tukee kaikkia Unicode-merkkejä, myös emojeja.

¶Menetelmä 1: phpMyAdmin

Vaihda oletus koko tietokannalle (koskee vain uusia tauluja):

1. Kirjaudu phpMyAdminiin ja valitse tietokanta vasemmalla puolella.
2. Napsauta Toiminnot-välilehteä.
3. Etsi osio Lajittelu (Collation).
4. Valitse utf8mb4_unicode_ci pudotusvalikosta.
5. Napsauta Suorita.

Vaihda olemassa oleva taulu (sisältäen kaikki sen sarakkeet):

1. Valitse taulu vasemmalla puolella.
2. Napsauta Toiminnot-välilehteä.
3. Osiossa Taulun valinnat valitaan uusi lajittelu.
4. Ruksaa Vaihda myös lajittelu kaikille sarakkeille.
5. Napsauta Suorita.

¶Menetelmä 2: SQL

Vaihda tietokannan oletus:

ALTER DATABASE mindatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Vaihda taulu ja kaikki sen sarakkeet:

ALTER TABLE mintabel CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

phpMyAdminissa voit vaihtaa charset per sarake: valitse taulu, napsauta Rakenne, napsauta sarakkeen vieressä olevaa Vaihda, ja valitse uusi lajittelu sarakemäärittelyssä.

¶Muunna kaikki taulut kerralla

Luo ALTER-komennot automaattisesti:

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mindatabase' AND TABLE_TYPE = 'BASE TABLE';

Suorita tulos phpMyAdminissa SQL-välilehden alla tai komentoriviltä.

¶Yhteyden charset (SET NAMES)

Tietokannan charset ja yhteyden charset ovat kaksi eri asiaa. Vaikka taulusi olisivat oikein asetettu utf8mb4:een, merkit voivat vioittua siirrossa, jos sovelluksen ja MySQL:n välinen yhteys käyttää eri charsetia.

SET NAMES utf8mb4 asettaa kolme istuntomuuttujaa kerralla:

• character_set_client – mitä palvelin odottaa vastaanottavansa asiakkaalta
• character_set_connection – mitä palvelin käyttää sisäisesti kyselyn käsittelyn aikana
• character_set_results – mitä palvelin lähettää takaisin asiakkaalle

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

Parempi käytäntö: käytä ohjaimen sisäänrakennettua charset-option sen sijaan, että lähettäisit SET NAMES manuaalisesti. Tämä varmistaa, että myös funktiot kuten mysqli_real_escape_string() ja PDO::quote() käyttävät oikeaa charsetia – muuten voit harvinaisissa tapauksissa avata SQL-injektiohaavoittuvuuden.