How To insert Non-English Text into MySQL Tables

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


CREATE TABLELangs(
LangIdchar(2) NOT NULL ,
KeyNamevarchar(30) NOT NULL,
KeyValuevarchar(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.