when I use CREATE TEMPORARY TABLE
to create a temporary table, the collation of this table is utf8_unicode_ci
by default. why by default it is utf8_unicode_ci
? How can I change the default?
The CREATE TABLE syntax allows for specifying the character set and collation
The MySQL Documentation for CREATE TABLE shows this syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] table_options: table_option [[,] table_option] ... table_option: ENGINE [=] engine_name | AUTO_INCREMENT [=] value | AVG_ROW_LENGTH [=] value | [DEFAULT] CHARACTER SET [=] charset_name <<------ (YOU NEED THIS OPTION) | CHECKSUM [=] {0 | 1} | [DEFAULT] COLLATE [=] collation_name <<------ (YOU NEED THIS OPTION) | COMMENT [=] 'string' | CONNECTION [=] 'connect_string' | DATA DIRECTORY [=] 'absolute path to directory' | DELAY_KEY_WRITE [=] {0 | 1} | INDEX DIRECTORY [=] 'absolute path to directory' | INSERT_METHOD [=] { NO | FIRST | LAST } | KEY_BLOCK_SIZE [=] value | MAX_ROWS [=] value | MIN_ROWS [=] value | PACK_KEYS [=] {0 | 1 | DEFAULT} | PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | STATS_AUTO_RECALC [=] {DEFAULT|0|1} | STATS_PERSISTENT [=] {DEFAULT|0|1} | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] | UNION [=] (tbl_name[,tbl_name]...)
Therefore, your syntax should be something like this:
CREATE TEMPORARY TABLE tblname
(
) ENGINE=MyISAM CHARACTER SET '...' COLLATE '...';
Give it a Try !!!
If you want to manipulate the default options for collation, you have the following:
These variables can be changed at the Global or Session Level.
If you want to set the collation permamnently, you could put it in /etc/my.cnf
[mysqld]
collation_connection = 'collation_connection'
and restart mysql
If you only want this for an individual BB Connection, you would run this in the session
SET collation_connection = 'collation_connection ';
See my recent post Why are there different levels of MySQL collation/charsets? to see installed character sets and collations.
normally the temp table takes the default from server/database level. if you want to change it, then change at the sever/DB level and this will impact all your other objects as well.
if you want it only once for your current temp table, then declare it strongly at your temp table creation process.
-
How can I change at the server/DB level? by which query or config? – Arash Mousavi Aug 1 '13 at 11:18
show variables like "%character%" ;
show variables like "%collation%";
and pay particular attention to the collation_database – KCD Aug 20 '13 at 19:43