mysql> SHOW VARIABLES LIKE 'CHAR%'; +--------------------------+-------------------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------------------+ | character_set_client | cp850 | | character_set_connection | cp850 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | cp850 | | character_set_server | utf8mb4 | | character_set_system | utf8mb3 | | character_sets_dir | E:\wamp64\bin\mysql\mysql8.0.33\share\charsets\ | +--------------------------+-------------------------------------------------+ 8 rows in set, 1 warning (0.01 sec) mysql> SHOW VARIABLES LIKE 'colla%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | cp850_general_ci | | collation_database | utf8mb4_0900_ai_ci | | collation_server | utf8mb4_0900_ai_ci | +----------------------+--------------------+ 3 rows in set, 1 warning (0.00 sec)- 2 - PHP 8.2.7 et MySQL 5.7.42
mysql> SHOW VARIABLES LIKE 'CHAR%'; +--------------------------+-------------------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------------------+ | character_set_client | cp850 | | character_set_connection | cp850 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | cp850 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | E:\wamp64\bin\mysql\mysql5.7.42\share\charsets\ | +--------------------------+-------------------------------------------------+ 8 rows in set, 1 warning (0.00 sec) mysql> SHOW VARIABLES LIKE 'colla%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | cp850_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set, 1 warning (0.00 sec)On peut déjà noter qu'il y a des différences dans les jeux de caratères par défaut entre MySQL 5.7 et MySQL 8.0
DROP TABLE IF EXISTS table_test; CREATE TABLE IF NOT EXISTS table_test ( champ1 varchar(50) COLLATE latin1_general_ci NOT NULL, champ2 varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, champ3 varchar(50) CHARACTER SET latin2 NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
CREATE TABLE t1 ( col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, col2 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ) CHARACTER SET utf8;
ALTER TABLE t1 DEFAULT CHARACTER SET utf8mb4, MODIFY col1 CHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, MODIFY col2 CHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;Plus simplement, on peut utiliser :
ALTER TABLE t1 CONVERT TO CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci (ou utf8mb4_bin);Sans être obligé de donner les noms des colonnes ; seules les colonnes nécessitant d'être transcodées le seront automatiquement.
SET NAMES utf8mb4;au lieu de
SET NAMES utf8;avant les requêtes.