In which charset the data will be stored in database

[Shreelakshmi G]

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.