散分~~!
这个脚本本来是测试表名大小写的,结果几个人都出现了诡异的结果。
所以不但能测试你新年人品,还能测你是否遇见鬼了,哈哈,祝心情愉快,元旦快乐哦~~select @@version ;
select @@version_compile_os ;
select @@version_compile_machine ;
select @@lower_case_table_names ;
use test ;
create table Tb(id int);
select table_name, engine, version from information_schema.tables where table_name='Tb';
select table_name, engine, version from information_schema.tables where table_name='tb';
drop table Tb;
select table_name, engine, version from information_schema.tables where table_name='Tb';
select table_name, engine, version from information_schema.tables where table_name='tb';怎么最多只能给100分么?
这个脚本本来是测试表名大小写的,结果几个人都出现了诡异的结果。
所以不但能测试你新年人品,还能测你是否遇见鬼了,哈哈,祝心情愉快,元旦快乐哦~~select @@version ;
select @@version_compile_os ;
select @@version_compile_machine ;
select @@lower_case_table_names ;
use test ;
create table Tb(id int);
select table_name, engine, version from information_schema.tables where table_name='Tb';
select table_name, engine, version from information_schema.tables where table_name='tb';
drop table Tb;
select table_name, engine, version from information_schema.tables where table_name='Tb';
select table_name, engine, version from information_schema.tables where table_name='tb';怎么最多只能给100分么?
+------------------+
| @@version |
+------------------+
| 5.1.52-community |
+------------------+
1 row in set (0.05 sec)mysql> select @@version_compile_os ;
+----------------------+
| @@version_compile_os |
+----------------------+
| Win32 |
+----------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_machine ;
+---------------------------+
| @@version_compile_machine |
+---------------------------+
| ia32 |
+---------------------------+
1 row in set (0.00 sec)mysql> select @@lower_case_table_names ;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)mysql> use test ;
Database changed
mysql> create table Tb(id int);
Query OK, 0 rows affected (0.25 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | MyISAM | 10 |
+------------+--------+---------+
1 row in set (0.30 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | MyISAM | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)mysql> drop table Tb;
Query OK, 0 rows affected (0.03 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='Tb';
Empty set (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='tb';
Empty set (0.01 sec)mysql>
mysql> select @@version ;
+------------------+
| @@version |
+------------------+
| 5.1.46-community |
+------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_os ;
+----------------------+
| @@version_compile_os |
+----------------------+
| Win32 |
+----------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_machine ;
+---------------------------+
| @@version_compile_machine |
+---------------------------+
| ia32 |
+---------------------------+
1 row in set (0.00 sec)mysql> select @@lower_case_table_names ;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)mysql> use test ;
Database changed
mysql> create table Tb(id int);
Query OK, 0 rows affected (0.13 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| Tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.03 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)mysql> drop table Tb;
Query OK, 0 rows affected (0.05 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| Tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='tb';
Empty set (0.00 sec)mysql>
+----------------------+
| @@version |
+----------------------+
| 5.1.53-community-log |
+----------------------+
1 row in set (0.00 sec)localhost@root>select @@version_compile_os ;
+----------------------+
| @@version_compile_os |
+----------------------+
| Win32 |
+----------------------+
1 row in set (0.00 sec)localhost@root>select @@version_compile_machine ;
+---------------------------+
| @@version_compile_machine |
+---------------------------+
| ia32 |
+---------------------------+
1 row in set (0.00 sec)localhost@root>select @@lower_case_table_names ;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)localhost@root>use test ;
Database changed
localhost@root>create table Tb(id int);
Query OK, 0 rows affected (0.05 sec)localhost@root>select table_name, engine, version from information_schema.tables where table_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)localhost@root>select table_name, engine, version from information_schema.tables where table_name='tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)localhost@root>drop table Tb;
Query OK, 0 rows affected (0.05 sec)localhost@root>select table_name, engine, version from information_schema.tables where table_name='Tb';
Empty set (0.00 sec)localhost@root>select table_name, engine, version from information_schema.tables where table_name='tb';
Empty set (0.00 sec)
+----------------------+
| @@version |
+----------------------+
| 5.1.50-community-log |
+----------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_os ;
+----------------------+
| @@version_compile_os |
+----------------------+
| Win32 |
+----------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_machine ;
+---------------------------+
| @@version_compile_machine |
+---------------------------+
| ia32 |
+---------------------------+
1 row in set (0.00 sec)mysql> select @@lower_case_table_names ;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)mysql> use test ;
Database changed
mysql> create table Tb(id int);
Query OK, 0 rows affected (0.11 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.08 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.03 sec)mysql> drop table Tb;
Query OK, 0 rows affected (0.06 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='Tb';
Empty set (0.01 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='tb';
Empty set (0.03 sec)mysql>
+----------------------+
| @@version |
+----------------------+
| 5.0.51b-community-nt |
+----------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_os;
+----------------------+
| @@version_compile_os |
+----------------------+
| Win32 |
+----------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_machine;
+---------------------------+
| @@version_compile_machine |
+---------------------------+
| ia32 |
+---------------------------+
1 row in set (0.00 sec)mysql> use test
Database changed
mysql> create table Tb(id int);
Query OK, 0 rows affected (0.19 sec)mysql> select table_name,engine,version from information_schema.tables where tab
le_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
| tb | InnoDB | 10 |
+------------+--------+---------+
2 rows in set (0.15 sec)mysql> select table_name,engine,version from information_schema.tables where tab
le_name='tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
| tb | InnoDB | 10 |
+------------+--------+---------+
2 rows in set (0.01 sec)mysql> drop table Tb;
Query OK, 0 rows affected (0.04 sec)mysql> Select table_name,engine version from information_schema.tables where tab
le_name='Tb';
+------------+---------+
| table_name | version |
+------------+---------+
| tb | InnoDB |
+------------+---------+
1 row in set (0.01 sec)mysql> Select table_name,engine version from information_schema.tables where tab
le_name='tb';
+------------+---------+
| table_name | version |
+------------+---------+
| tb | InnoDB |
+------------+---------+
1 row in set (0.01 sec)
mysql> select @@version ;
+----------------------+
| @@version |
+----------------------+
| 5.1.36-community-log |
+----------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_os ;
+----------------------+
| @@version_compile_os |
+----------------------+
| Win32 |
+----------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_machine ;
+---------------------------+
| @@version_compile_machine |
+---------------------------+
| ia32 |
+---------------------------+
1 row in set (0.00 sec)mysql> select @@lower_case_table_names ;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)mysql> use test ;
Database changed
mysql> create table Tb(id int);
Query OK, 0 rows affected (0.13 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| Tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.06 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)mysql> drop table Tb;
Query OK, 0 rows affected (0.03 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| Tb | InnoDB | 10 |
+------------+--------+---------+
mysql> select table_name, engine, version from information_schema.tables where t
able_name='tb';
Empty set (0.02 sec)mysql>
+---------------------+
| @@version |
+---------------------+
| 5.0.45-community-nt |
+---------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_os ;
+----------------------+
| @@version_compile_os |
+----------------------+
| Win32 |
+----------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_machine ;
+---------------------------+
| @@version_compile_machine |
+---------------------------+
| ia32 |
+---------------------------+
1 row in set (0.00 sec)mysql> select @@lower_case_table_names ;
ERROR 1193 (HY000): Unknown system variable 'lower_case_table_names'
mysql> use test ;
Database changed
mysql> create table Tb(id int);
Query OK, 0 rows affected (0.06 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | MyISAM | 10 |
+------------+--------+---------+
1 row in set (0.02 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | MyISAM | 10 |
+------------+--------+---------+
1 row in set (0.02 sec)mysql> drop table Tb;
Query OK, 0 rows affected (0.03 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='Tb';
Empty set (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='tb';
Empty set (0.00 sec)mysql>
确实很诡异的
Option-File Format lower_case_table_names
Option Sets Variable Yes, lower_case_table_names
Variable Name lower_case_table_names
Variable Scope Global
Dynamic Variable No
Permitted Values
Type numeric
Default 0
Range 0-2
If set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional information, see Section 8.2.2, “Identifier Case Sensitivity”.You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result. On Windows the default value is 1. On Mac OS X, the default value is 2.主要是默认值在不同的os上不一样哈!
我认为
1,4,5,楼正常
3楼诡异
7楼应该也正常不过哪来的另外的tb呢?
11楼竟然出现Tb了!注意是win版的innodb库
13楼正常,但 select @@lower_case_table_names ;竟然出错???
mysql> select @@version ;
+------------------+
| @@version |
+------------------+
| 5.1.31-community |
+------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_os ;
+----------------------+
| @@version_compile_os |
+----------------------+
| Win32 |
+----------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_machine ;
+---------------------------+
| @@version_compile_machine |
+---------------------------+
| ia32 |
+---------------------------+
1 row in set (0.00 sec)mysql> select @@lower_case_table_names ;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)mysql> use test ;
Database changed
mysql> create table Tb(id int);
Query OK, 0 rows affected (0.09 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| Tb | InnoDB | 10 |
| Tb | InnoDB | 10 |
+------------+--------+---------+
2 rows in set (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
| tb | InnoDB | 10 |
+------------+--------+---------+
2 rows in set (0.00 sec)mysql> drop table Tb;
Query OK, 0 rows affected (0.08 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| Tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)mysql>
mysql> select @@version ;
select @@version_compile_os ;
select @@version_compile_machine ;
select @@lower_case_table_names ;
use test ;
create table Tb(id int);
select table_name, engine, version from information_schema.tables where table_name='Tb';
select table_name, engine, version from information_schema.tables where table_name='tb';
drop table Tb;
select table_name, engine, version from information_schema.tables where table_name='Tb';
select table_name, engine, version from information_schema.tables where table_name='tb';
+--------------------------------+
| @@version |
+--------------------------------+
| 6.0.2-alpha-community-nt-debug |
+--------------------------------+
1 row in set+----------------------+
| @@version_compile_os |
+----------------------+
| Win32 |
+----------------------+
1 row in set+---------------------------+
| @@version_compile_machine |
+---------------------------+
| ia32 |
+---------------------------+
1 row in set1193 - Unknown system variable 'lower_case_table_names'
Database changed
Query OK, 0 rows affected+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in setQuery OK, 0 rows affectedEmpty setEmpty set
同样异常估计和电脑环境有关系
mysql> select @@version ;
+------------------+
| @@version |
+------------------+
| 5.1.50-community |
+------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_os ;
+----------------------+
| @@version_compile_os |
+----------------------+
| Win32 |
+----------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_machine ;
+---------------------------+
| @@version_compile_machine |
+---------------------------+
| ia32 |
+---------------------------+
1 row in set (0.00 sec)mysql> select @@lower_case_table_names ;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)mysql> use test ;
ERROR 1049 (42000): Unknown database 'test'
mysql> create table Tb(id int);
Query OK, 0 rows affected (0.09 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.02 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| tb | InnoDB | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)mysql> drop table Tb;
Query OK, 0 rows affected (0.03 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='Tb';
Empty set (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where t
able_name='tb';
Empty set (0.02 sec)
mysql> select @@version ;
+------------------------+
| @@version |
+------------------------+
| 5.1.41-3ubuntu12.8-log |
+------------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_os ;
+----------------------+
| @@version_compile_os |
+----------------------+
| debian-linux-gnu |
+----------------------+
1 row in set (0.00 sec)mysql> select @@version_compile_machine ;
+---------------------------+
| @@version_compile_machine |
+---------------------------+
| x86_64 |
+---------------------------+
1 row in set (0.00 sec)mysql> select @@lower_case_table_names ;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (0.00 sec)mysql> use test ;
Database changed
mysql> create table Tb(id int);
Query OK, 0 rows affected (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='Tb';
+------------+--------+---------+
| table_name | engine | version |
+------------+--------+---------+
| Tb | MyISAM | 10 |
+------------+--------+---------+
1 row in set (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='tb';
Empty set (0.01 sec)mysql> drop table Tb;
Query OK, 0 rows affected (0.00 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='Tb';
Empty set (0.01 sec)mysql> select table_name, engine, version from information_schema.tables where table_name='tb';
Empty set (0.01 sec)
-- 5.1.41-logselect @@version_compile_os ;
-- unknown-linux-gnuselect @@version_compile_machine ;
-- x86_64select @@lower_case_table_names ;
-- 0use test ;
create table Tb(id int);
select table_name, engine, version from information_schema.tables where table_name='Tb';
-- 'Tb', 'MyISAM', '10'select table_name, engine, version from information_schema.tables where table_name='tb';drop table Tb;
select table_name, engine, version from information_schema.tables where table_name='Tb';
select table_name, engine, version from information_schema.tables where table_name='tb';
18楼正常,但同样不识别select @@lower_case_table_names ; 这个命令,为什么有的mysql会这样的情况捏?(出现这种情况的是5.0.45和6.0版本)
20,22楼正常楼正常
22,23楼用的linux,但23楼没有给出测试结果