Characters stored as weird characters in database but are rendered properly in HTML

Question

I am trying to store the string "ಕನ್ನಡ" into mySQL database. It is getting stored as "ಕನà³à²¨à²¡" But it is rendering properly as "ಕನ್ನಡ" on html. Is it possible to store it as "ಕನ್ನಡ" instead of "ಕನà³à²¨à²¡" on database? IF yes. how? If you need more information about collation ,encoding please ask.

The column's charset that stores the string is 'utf8_unicode_ci'

Answer 1

the fact that you can retrieve them and show them correctly means that they are being correctly stored.

I would guess that the other tool that you are using to verify the data is show the data incorrectly.

This is probably a problem in PhpMYAdmin's connection settings. It must be explicitly set to UTF-8 as well. See the accepted answer in How to display UTF-8 characters in phpMyAdmin?

Answer 2

For How to set default character set and collation to utf, add following lines described sections in my.cnf and restart MySQL Service.

[client]
default-character-set   = utf8

[mysql]
default-character-set   = utf8

[mysqld]
character-set-server    =       utf8
collation-server        =       utf8_general_ci
init-connect            =       'SET NAMES utf8'

Remember that already existing database & table will not change there charset and collation, and tables created inside existing databases will have charset of that database.

But newly created database and tables will have default charset and collation of utf.

Example :

mysql> create database demo1;
Query OK, 1 row affected (0.00 sec)

mysql> show create database demo1 \G
*************************** 1. row ***************************
       Database: demo1
Create Database: CREATE DATABASE `demo1` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

mysql> use demo1 
Database changed

mysql> create table test_char(data_to_insert CHAR(50)) ;
Query OK, 0 rows affected (0.26 sec)

mysql> show create table test_char\G
*************************** 1. row ***************************
       Table: test_char
Create Table: CREATE TABLE `test_char` (
  `data_to_insert` char(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> INSERT INTO test_char VALUES('ಕನ್ನಡ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_char;
+-----------------+
| data_to_insert  |
+-----------------+
| ಕನ್ನಡ            |
+-----------------+
1 row in set (0.00 sec)