Here, I would like to highlight difference between 2 methods that can be used to convert character sets Doing at table level:įollowing command can be used to convert character set at table level ALTER TABLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci We can use multiple approaches for converting character set for existing data and percona blog has provided a very good reference for these methods along with advantages and disadvantages – Now, for changing character set for existing data, we need to change character set for every text columns of every table in database where we want to change it Changing Character set of existing data:Ībove steps are required for setting the character set at various level so that future objects and data will be created in UTF8 This will take effect only for new columns getting added to the table. So, if you have many tables in database, you can use following command to dynamically generate a script which can be used to set character set for all required tables in database SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8 COLLATE utf8_unicode_ci ") AS alter_sqlīut this will not change existing columns/data in the table. ALTER TABLE CHARACTER SET utf8 COLLATE utf8_unicode_ci We can use alter table command to set the character set for a table. This will only take affect for future objects/tables that we create in this database. We can use following command:- ALTER DATABASE DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci Īgain, this does not affect existing data or objects. We can alter any database on our MySQL server and change the character set to UTF8. Only new database creation will take this effect. But this does not change anything in existing data or objects.
Once MySQL server is bounced it will pick new value of this parameter and new character set will be the one we set for this parameter. We can do this by setting parameter character_set_server in our main my.cnf file. We can set character set at following level Server Level: Example – the return values of the USER(), CURRENT_USER(), SESSION_USER(), SYSTEM_USER(), DATABASE(), VERSION() etc will be retuned in character set assigned to this variable.Ĭharacter_sets_dir – The directory where all character sets are installed. Any new database created will used this character set, unless we are defining character set at database levelĬharacter_set_system – The character set used by the server for storing metadata infomration. If client has used character set in its connection, then this value will not be used for returning the result.Ĭharacter_set_server – Character set defined at the server level. This character set will be used whenever we change database on server and if that database does not have any character set defined.Ĭharacter_set_filesystem – This character set is used to interpret string literals that refer to file names at file system level, such as in the LOAD DATA INFILE and SELECT … INTO OUTFILE statements.Ĭharacter_set_results – The character set used for returning query results such as result sets. Else value set by client during connection will override this valueĬharacter_set_connection – The character set used for literals that do not have a character set introducer and for number-to-string conversion.Ĭharacter_set_database – Character set used by default database. If client is not setting any character set while connecting, this character set will be used for statements send by client. | character_sets_dir | /usr/local/mysql-5.6.16-linux-glibc2.5-x86_64/share/charsets/ |Ĭharacter_set_client – The character set for statements that arrive from the client. Root >show variables like '%character%set%' We can see following parameters for character sets Make sure your backups and restore methods are proven, meaning that you have sucecssfully done restore of tables/database etc Setting Character set at various level:
In case something goes wrong, we can always have data and recreate requried table/database etc. Backup your database:īefore even thinking about changing character set for your database, take backup of database using whatever backup method that is tested by you – mysqldump, mysql-enterprise-backup, export etc
#Command mysql create database utf8 how to
This article describes the approach taken for doing the same.įirst I will describe various levels at which we can change the character set and collation and then we will see how to convert existing data in a database to required character set. Recently I was engaged to convert the character set for few MySQL databases from latin1 to UTF8 collation utf8_general_ci.
0 kommentar(er)
