sql_mode.test
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:3k
- --disable_warnings
- drop table if exists t1;
- --enable_warnings
- CREATE TABLE `t1` (
- a int not null auto_increment,
- `pseudo` varchar(35) character set latin2 NOT NULL default '',
- `email` varchar(60) character set latin2 NOT NULL default '',
- PRIMARY KEY (a),
- UNIQUE KEY `email` USING BTREE (`email`)
- ) ENGINE=HEAP CHARSET=latin1 ROW_FORMAT DYNAMIC;
- set @@sql_mode="";
- show variables like 'sql_mode';
- show create table t1;
- set @@sql_mode="ansi_quotes";
- show variables like 'sql_mode';
- show create table t1;
- set @@sql_mode="no_table_options";
- show variables like 'sql_mode';
- show create table t1;
- set @@sql_mode="no_key_options";
- show variables like 'sql_mode';
- show create table t1;
- set @@sql_mode="no_field_options,mysql323,mysql40";
- show variables like 'sql_mode';
- show create table t1;
- set sql_mode="postgresql,oracle,mssql,db2,maxdb";
- select @@sql_mode;
- show create table t1;
- drop table t1;
- #
- # Check that a binary collation adds 'binary'
- # suffix into a char() column definition in
- # mysql40 and mysql2323 modes. This allows
- # not to lose the column's case sensitivity
- # when loading the dump in pre-4.1 servers.
- #
- # Thus, in 4.0 and 3.23 modes we dump:
- #
- # 'char(10) collate xxx_bin' as 'char(10) binary'
- # 'binary(10)' as 'binary(10)'
- #
- # In mysql-4.1 these types are different, and they will
- # be recreated differently.
- #
- # In mysqld-4.0 the the above two types were the same,
- # so it will create a 'char(10) binary' column for both definitions.
- #
- CREATE TABLE t1 (
- a char(10),
- b char(10) collate latin1_bin,
- c binary(10)
- ) character set latin1;
- set @@sql_mode="";
- show create table t1;
- set @@sql_mode="mysql323";
- show create table t1;
- set @@sql_mode="mysql40";
- show create table t1;
- drop table t1;
- #
- # BUG#5318 - failure: 'IGNORE_SPACE' affects numeric values after DEFAULT
- #
- # Force the usage of the default
- set session sql_mode = '';
- # statement for comparison, value starts with '.'
- create table t1 ( min_num dec(6,6) default .000001);
- show create table t1;
- drop table t1 ;
- #
- set session sql_mode = 'IGNORE_SPACE';
- # statement for comparison, value starts with '0'
- create table t1 ( min_num dec(6,6) default 0.000001);
- show create table t1;
- drop table t1 ;
- # This statement fails, value starts with '.'
- create table t1 ( min_num dec(6,6) default .000001);
- show create table t1;
- drop table t1 ;
- #
- # Bug #10732: Set SQL_MODE to NULL gives garbled error message
- #
- --error 1231
- set @@SQL_MODE=NULL;
- #
- # Bug #797: in sql_mode=ANSI, show create table ignores auto_increment
- #
- set session sql_mode=ansi;
- create table t1
- (f1 integer auto_increment primary key,
- f2 timestamp default current_timestamp on update current_timestamp);
- show create table t1;
- set session sql_mode=no_field_options;
- show create table t1;
- drop table t1;
- # End of 4.1 tests