MySQL Charset and Collations

You need to have a good apprehending of what is unicode,encoding ??


VARIABLES WORKING IN MYSQL TO FORM A PERFECT COLLATION AND CHARSET


1) To know server Charset and Collation  
--character-set-server
--collation-server

SHOW VARIABLES LIKE 'Character_set_server'; 
SHOW VARIABLES LIKE 'Collation_server';

This parameters can be set at OS level

2) To know DB Charset and Collation 


SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

3)  TO Know Table Charset and Collation


SELECT TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE LOWER(TABLE_SCHEMA)='<DB_Name>' AND 
LOWER(Table_Name)='<Table_Name>';

4) TO Know Column Charset and Collation(For All columns)


SHOW FULL FIELDS FROM `<DB_Name>`.`<Table_Name>`;

5) VARIABLE PLAYS PART IN MYSQL 


Client side Charset how to know


SHOW VARIABLES LIKE 'character_set_client'; 

After receiving the statment server uses 2 parameters


character_set_connection 
collation_connection

Server use this Charset to send result and errors

Character_set_results




SET NAMES <charset name> COLLATE <collation_Name> will send the charset to send and receive the data from clients


We can say 


SET NAMES <charset name> IS same as 


SET character_set_client = <charset name>;
SET character_set_results = <charset name>;
SET character_set_connection = <charset name>;




When a client connects to the server, it sends the name of the character set that it wants to use. The server uses the name to set the character_set_client, character_set_results, and character_set_connection system variables.


If you do not want the server to perform any conversion of result sets or error messages, set character_set_results to NULL or binary: