Introduction
When we work with web application that may be accessed globally, we may need to display messages in non-English characters and may need to store these non-English characters in our database tables. In this article, I will explain how to store non-English characters in MySQL.
Lets first see why it is necessary with an example.
Let create a simple table, which has three columns.
Create table Langs
(
LangId char(2),
KeyName varchar(20),
KeyValue varchar(50)
);
Lets Insert a few values in English, Catalan, German, Hindi and Russian into this newly created table as follows;
Insert into Langs(LangId, KeyName, KeyValue) values('EN','userid_required','User ID required!');
Insert into Langs(LangId, KeyName, KeyValue) values('CA','userid_required','Es requereix identificador d\'usuari!');
Insert into Langs(LangId, KeyName, KeyValue) values('GE','userid_required','Benutzerkennung erforderlich!');
Insert into Langs(LangId, KeyName, KeyValue) values('HD','userid_required','यूजर आईडी जरूरी!');
Insert into Langs(LangId, KeyName, KeyValue) values('RS','userid_required','Требуется идентификатор пользователя!');
Notice the \' in the d\'usuari!. We have used the backward slash in front of the apostrophe to include the special apostrophe character in our data.
Let see how the data will show up in our Langs MySQL table.
As we can see the non-English data got stored in ??? which is not what we want.
We can configure MySQL to accept non-English characters in two ways
Configuring at the Table Level.
While creating the table, we can specify that the tables charset is utf8mb4, which will allow us to store non-English characters in the table. Note this effect the entire table column and not just a specific column.
Lets drop that table and recreate it this time, and specifying that the tables charset set is utf8mb4
Langs
CREATE TABLE(
LangId
char(2) NOT NULL ,
KeyName
varchar(30) NOT NULL,
KeyValue
varchar(100) NOT NULL
) DEFAULT CHARSET=utf8mb4;
Lets try re-inserting the values in English, Catalan, German, Hindi and Russian as before;
Insert into Langs(LangId, KeyName, KeyValue) values('EN','userid_required','User ID required!');
Insert into Langs(LangId, KeyName, KeyValue) values('CA','userid_required','Es requereix identificador d\'usuari!');
Insert into Langs(LangId, KeyName, KeyValue) values('GE','userid_required','Benutzerkennung erforderlich!');
Insert into Langs(LangId, KeyName, KeyValue) values('HD','userid_required','यूजर आईडी जरूरी!');
Insert into Langs(LangId, KeyName, KeyValue) values('RS','userid_required','Требуется идентификатор пользователя!');
Now we got the non-English characters stored properly into the tables.
Configuring at the Database Level.
If we have many tables that need to store non-English characters, then we can configure the database while creating it as follows;
You can do this at the database creation:
CREATE DATABASE mydb
DEFAULT CHARACTER SET utf8mb4
Now we don't have to specify the CHARSET=utf8mb4 for individual tables.
We can also alter a existing database with the alter database command
ALTER DATABASE mydb
DEFAULT CHARSET utf8mb4;
Conclusion
To store the non-English Characters or Multi language in MySQL database table, we can configure it in two ways. Either at the table level, or the database itself. Character set utf8mb4 helps us to store non-English characters into the MySQL tables.
I hope you all found this article useful. Please share your feedback in the comment section.
Top comments (0)