Se alguém já se deparou com esse problema, sabe que é devido ao fato de tentarmos inserir um ’emoticon’ em um coluna de nosso banco de dados.
Ao criarmos um banco de dados não nos importamos inicialmente com o CHARSET
e COLLATE
dele, porém é de extrema importância dependendo de seu uso. Normalmente o banco de dados vem definido com o Charset
: latin1
e o COLLATE
: latin1_swedish_ci
, o que devemos fazer é altera-lo para utf8mb4
e utf8mb4_general_ci
respectivamente.
Veja o que são
CHARSET
eCOLLATE
: https://mariadb.com/kb/en/character-set-and-collation-overview/
A idéia não é mostrar como inicializar um banco corretamente, então irei mostrar como alterar nossa coluna para que seja possível a inserção do emoticon
.
Primeiramente podemos verificar o charset e collate do banco com o seguinte comando:
mysql> SELECT DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA S WHERE schema_name = "my_db";
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| latin1 | latin1_swedish_ci |
+----------------------------+------------------------+
Para verificar o COLLATE
de cada coluna de nossa tabela iremos executar a seguinte instrução:
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'my_table';
+--------------+------------+-------------+-------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME |
+--------------+------------+-------------+-------------------+
| raven | my_table | note | latin1_swedish_ci |
+--------------+------------+-------------+-------------------+
Também devemos verificar o CHARSET
da tabela:
mysql> SHOW CREATE TABLE my_table;
+---------+------------------------------+
| Table | Create Table |
+---------+------------------------------+
| teste | CREATE TABLE `teste` (
`note` varchar(300) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+------------------------------+
Note o COLLATE
definido na coluna note
, e o CHARSET
definido na tabela não suportam a inserção de emoticons pelo fato de usarem o latin1
.
Agora devemos realizar as alterações necessárias para alterar o valor default do CHARSET
e do COLLATE
mysql> ALTER DATABASE my_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Agora que o valor default
do banco de dados foi alterado, toda a criação de novas tabelas já vai estar com as configurações adequadas. Porém ainda vamos ter problemas com as tabelas antigas, para isso iremos realizar os seguintes procedimentos:
Faça a conversão da tabela:
mysql> ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
A conversão ira ajustar todas as colunas de sua tabela, porém pode acontecer alguns problemas caso tente converter algumas Foreign Keys , no caso deveriamos converter essas outras tabelas primeiro.
Caso você queira ajustar somente algumas colunas também é possível realizar a conversão específica do valor da coluna e do valor default da tabela separadamente:
// Alterando apenas o valor default da tabela:
mysql> ALTER TABLE my_table CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
// Alterando apenas o COLLATE de uma coluna específica:
mysql> ALTER TABLE my_table MODIFY note VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Após essas alterações sua tabela já estará preparada para a inserção de emoticons. Porém, sua conexão também possui a definição de CHARSET
e COLLATE
que são armazenadas nas variáveis de ambiente do banco (normalmente a conexão é aberta com utf8
no Linux), por esse motivo é extremamente recomendado que ao abrir a conexão com seu banco de dados você defina-o também atravéz do seguinte comando:
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
Ou diretamente ná abertura da conexão:
user@user:~$ mysql --default-character-set=utf8mb4 -h127.0.0.1 -u root -p
Se você estiver usando o Spring Boot por exemplo, você pode definir a execução desse comando no
application.properties
com a seguinte instrução:spring.datasource.hikari.connection-init-sql="SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;"
Curiosidade: Essas variáveis podem ser observadas pelo comando:
mysql> SELECT @@character_set_client;
+------------------------+
| @@character_set_client |
+------------------------+
| utf8mb4 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_unicode_ci |
+------------------------+
1 row in set (0.00 sec)
Agora você consegue inserir seus emoticons sem problemas:
mysql> INSERT INTO my_table(note) values('emoticons: 😄 , 😖 , 😀');
Referências:
https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html
https://dev.mysql.com/doc/refman/8.0/en/charset-database.html
https://mariadb.com/kb/en/character-set-and-collation-overview/
Muuito bom o artigo !
Parabéns !
Boa, Biff.
Eu já tinha me deparado com esse problema na minha instalação Nextcloud (que usa um app para chat – Nextcloud Talk) e resolvi assim.
Adicionei ao final de /etc/mysql/my.cnf as linhas abaixo.
[mysqld]
innodb_file_per_table=1
Restartei o serviço, loguei como root e executei os comandos abaixo:
alter database db-name character set utf8mb4 collate utf8mb4_general_ci;
use db-name;
set global innodb_file_format=Barracuda;
quit;
Abraço.
Fala Zeca, boaa, valeu a dica do `my.cnf`, com certeza vai ser útil;
Abraço.
Boa tarde, estou precisando muito resolver esta situação no meu sistema, fiz tudo isso e não da certo, qual a opção correta, utf8mb4_unicode_ci ou utf8mb4_general_ci? e o tipo do campo para gravação (text ou Blob).