1'#--------------------FN_DYNVARS_011_01-------------------------#' 2SET @global_collation_database = @@global.collation_database; 3SET @session_collation_database = @@session.collation_database; 4SET @session_collation_server = @@session.collation_server; 5SET @@global.collation_database = latin1_danish_ci; 6connect con1,localhost,root,,,,; 7connection con1; 8SELECT @@global.collation_database; 9@@global.collation_database 10latin1_danish_ci 11SELECT @@session.collation_database; 12@@session.collation_database 13latin1_swedish_ci 14disconnect con1; 15'#--------------------FN_DYNVARS_011_02-------------------------#' 16connection default; 17DROP TABLE IF EXISTS t1,t2; 18'--check if setting collation_database update character_set_database--' 19SET @@session.collation_database = utf8_spanish_ci; 20SELECT @@collation_database, @@character_set_database; 21@@collation_database @@character_set_database 22utf8_spanish_ci utf8 23'--check if collation_database effects database/tables charset/collation' 24SET @@session.collation_server = utf8_roman_ci; 25SET @@session.collation_database = latin2_croatian_ci; 26CREATE DATABASE db1; 27USE db1; 28SHOW CREATE DATABASE db1; 29Database Create Database 30db1 CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_roman_ci */ 31CREATE TABLE t1(a CHAR(20)); 32SHOW CREATE TABLE t1; 33Table Create Table 34t1 CREATE TABLE `t1` ( 35 `a` char(20) COLLATE utf8_roman_ci DEFAULT NULL 36) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_roman_ci 37'---check if updating collation_database effects new table/column---' 38SET @@session.collation_database = latin7_general_cs; 39CREATE TABLE t2(a CHAR(10)); 40SHOW CREATE TABLE t2; 41Table Create Table 42t2 CREATE TABLE `t2` ( 43 `a` char(10) COLLATE utf8_roman_ci DEFAULT NULL 44) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_roman_ci 45DROP TABLE t1,t2; 46'--check value of collation and character set when current db is dropped' 47DROP DATABASE db1; 48SELECT @@collation_database,@@collation_server,@@character_set_database,@@character_set_server; 49@@collation_database @@collation_server @@character_set_database @@character_set_server 50utf8_roman_ci utf8_roman_ci utf8 utf8 51USE test; 52SELECT @@collation_database,@@collation_server,@@character_set_database,@@character_set_server; 53@@collation_database @@collation_server @@character_set_database @@character_set_server 54latin1_swedish_ci utf8_roman_ci latin1 utf8 55'fill table with some test data'; 56CREATE TABLE t1(a CHAR(20))CHARACTER SET=latin1; 57INSERT INTO t1 VALUES('Muffler'),('M�ller'),('MX Systems'); 58'---check if collation_database effects results sort order---' 59SET @@session.collation_database = latin1_swedish_ci; 60SELECT * FROM t1 ORDER BY a; 61a 62Muffler 63MX Systems 64M�ller 65SET @@session.collation_database = latin1_german1_ci; 66SELECT * FROM t1 ORDER BY a; 67a 68Muffler 69MX Systems 70M�ller 71'explicit Collate clause should effects results sort order'; 72SELECT * FROM t1 ORDER BY a COLLATE latin1_swedish_ci; 73a 74Muffler 75MX Systems 76M�ller 77SELECT * FROM t1 ORDER BY a COLLATE latin1_german1_ci; 78a 79Muffler 80M�ller 81MX Systems 82'----check if indexing is effected by collation_database---' 83SET @@session.collation_database = latin1_swedish_ci; 84ALTER TABLE t1 ADD PRIMARY KEY (a); 85REPAIR TABLE t1; 86Table Op Msg_type Msg_text 87test.t1 repair status OK 88SELECT * FROM t1 ORDER BY a; 89a 90Muffler 91MX Systems 92M�ller 93ALTER TABLE t1 DROP PRIMARY KEY; 94SET @@session.collation_database = latin1_german1_ci; 95ALTER TABLE t1 ADD PRIMARY KEY (a); 96REPAIR TABLE t1; 97Table Op Msg_type Msg_text 98test.t1 repair status OK 99SELECT * FROM t1 ORDER BY a; 100a 101Muffler 102MX Systems 103M�ller 104DROP TABLE t1; 105SET @@global.collation_database = @global_collation_database; 106SET @@session.collation_database = @session_collation_database; 107SET @@session.collation_server = @session_collation_server; 108