In which charset the data will be stored in database
Posted by: Shreelakshmi G
Date: January 11, 2018 05:26AM
Date: January 11, 2018 05:26AM
My database is in latin1 and collation is latin1_swedish_ci. Also in my php file I'm using iso-8859-1. But If I store some characters like 'é' and some others I'm having problem in dowloading the content. So we have added the default character-set to utf8 and skip-character-set-client-handshake in my.cnf file. Also added 'set-name utf8' in query soon after every connection establishment and before executing any query. This solved the issue. But I have done some observations as below
Case 1:
Mysql conf file: No default character-set and no skip-character-set-client-handshake
Query: No set names
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
stored ->é(utf8)(Hex->C3A9)
Case2:
Mysql conf file: Default character-set - utf8 and no skip-character-set-client-handshake
Query: No set names
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
stored ->é(utf8)(Hex->C3A9)
Case3:
Mysql conf file: Default character-set - utf8 and skip-character-set-client-handshake
Query: No set names
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
stored ->é(latin1)(Hex->E9)
Case4:
Mysql conf file: no Default characterset - utf8 and skip-character-set-client-handshake
Query: No set names
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
stored ->é(utf8)(Hex->C3A9)
Case5:
Mysql conf file: Default characterset - utf8 and skip-character-set-client-handshake
Query: set names utf8
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
stored ->é(latin1)(Hex->E9)
Case6:
Mysql conf file: Default characterset - utf8 and no skip-character-set-client-handshake
Query: set names utf8
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
stored ->é(latin1)(Hex->E9)
Case7:
Mysql conf file: no Default characterset and no skip-character-set-client-handshake
Query: set names utf8
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
stored ->é(latin1)(Hex->E9)
Case8:
Mysql conf file: no Default characterset and skip-character-set-client-handshake
Query: set names utf8
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
stored ->é(latin1)(Hex->E9)
Output containing all the 8 cases together
+-----------+------------------------------------------------------------------+
| HEX(name) | desc |
+-----------+------------------------------------------------------------------+
| C3A9 | no skip handshake and no default in conf and nothing in query |
| C3A9 | no skip handshake and default utf8 in conf and nothing in query |
| E9 | skip handshake and default utf8 in conf and nothing in query |
| C3A9 | skip handshake and no default in conf and nothing in query |
| E9 | skip handshake and default utf8 in conf and utf8 in query |
| E9 | no skip handshake and default utf8 in conf and utf8 in query |
| E9 | no skip handshake and no default in conf and utf8 in query |
| E9 | skip handshake and no default in conf and utf8 in query |
+-----------+------------------------------------------------------------------+
On what basis the data is being stored in database? Sometimes its stored in latin1 format and sometimes in utf8.
Is it based on options(I mean variables like character_set_client, character_set_server etc...,) or On my.cnf configuration??
Considering all the 8 cases I'm not getting a conclusion on this.
I have also gone through the explaination on 'SET Names', 'skip-character-set-client-handshake'. But still in confusion. Is there any conversion happens while storing or just while displaying??
The above observations are done using a php script containing below lines.
--------------------------------------
$conn = mysqli_connect('<host>', '<username>', '<password>', 'table');
mysqli_query($conn, "SET NAMES 'utf8';");
mysqli_query($conn, 'insert into router.test values ("é");');
--------------------------------------
Thanks in advance for the replies.
Case 1:
Mysql conf file: No default character-set and no skip-character-set-client-handshake
Query: No set names
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
stored ->é(utf8)(Hex->C3A9)
Case2:
Mysql conf file: Default character-set - utf8 and no skip-character-set-client-handshake
Query: No set names
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
stored ->é(utf8)(Hex->C3A9)
Case3:
Mysql conf file: Default character-set - utf8 and skip-character-set-client-handshake
Query: No set names
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
stored ->é(latin1)(Hex->E9)
Case4:
Mysql conf file: no Default characterset - utf8 and skip-character-set-client-handshake
Query: No set names
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
stored ->é(utf8)(Hex->C3A9)
Case5:
Mysql conf file: Default characterset - utf8 and skip-character-set-client-handshake
Query: set names utf8
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
stored ->é(latin1)(Hex->E9)
Case6:
Mysql conf file: Default characterset - utf8 and no skip-character-set-client-handshake
Query: set names utf8
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
stored ->é(latin1)(Hex->E9)
Case7:
Mysql conf file: no Default characterset and no skip-character-set-client-handshake
Query: set names utf8
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
stored ->é(latin1)(Hex->E9)
Case8:
Mysql conf file: no Default characterset and skip-character-set-client-handshake
Query: set names utf8
Result:
mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
stored ->é(latin1)(Hex->E9)
Output containing all the 8 cases together
+-----------+------------------------------------------------------------------+
| HEX(name) | desc |
+-----------+------------------------------------------------------------------+
| C3A9 | no skip handshake and no default in conf and nothing in query |
| C3A9 | no skip handshake and default utf8 in conf and nothing in query |
| E9 | skip handshake and default utf8 in conf and nothing in query |
| C3A9 | skip handshake and no default in conf and nothing in query |
| E9 | skip handshake and default utf8 in conf and utf8 in query |
| E9 | no skip handshake and default utf8 in conf and utf8 in query |
| E9 | no skip handshake and no default in conf and utf8 in query |
| E9 | skip handshake and no default in conf and utf8 in query |
+-----------+------------------------------------------------------------------+
On what basis the data is being stored in database? Sometimes its stored in latin1 format and sometimes in utf8.
Is it based on options(I mean variables like character_set_client, character_set_server etc...,) or On my.cnf configuration??
Considering all the 8 cases I'm not getting a conclusion on this.
I have also gone through the explaination on 'SET Names', 'skip-character-set-client-handshake'. But still in confusion. Is there any conversion happens while storing or just while displaying??
The above observations are done using a php script containing below lines.
--------------------------------------
$conn = mysqli_connect('<host>', '<username>', '<password>', 'table');
mysqli_query($conn, "SET NAMES 'utf8';");
mysqli_query($conn, 'insert into router.test values ("é");');
--------------------------------------
Thanks in advance for the replies.
Subject
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.