-- DROP DATABASE IF EXISTS user04; DROP DATABASE IF EXISTS user06; CREATE DATABASE user04; CREATE DATABASE user06; DROP DATABASE IF EXISTS test; CREATE DATABASE test; use test; drop table if exists t1; create table t1(id int, name varchar(64)); insert into t1 values(1,'user02'),(2,'ddd'),(3,'user04'),(4,'dbaaa'),(5,'user06'); select * from t1; select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA; select name from t1 where name in (select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA); SHOW CREATE TABLE INFORMATION_SCHEMA.SCHEMATA\G -- 5.5.53 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.5.53: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.53-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> DROP DATABASE IF EXISTS user04; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> DROP DATABASE IF EXISTS user06; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE DATABASE user04; Query OK, 1 row affected (0.00 sec) mysql> CREATE DATABASE user06; Query OK, 1 row affected (0.00 sec) mysql> mysql> use test; Database changed mysql> drop table if exists t1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table t1(id int, name varchar(64)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(1,'user02'),(2,'ddd'),(3,'user04'),(4,'dbaaa'),(5,'user06'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------+--------+ | id | name | +------+--------+ | 1 | user02 | | 2 | ddd | | 3 | user04 | | 4 | dbaaa | | 5 | user06 | +------+--------+ 5 rows in set (0.00 sec) mysql> select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA; +--------------------+ | SCHEMA_NAME | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | user04 | | user06 | +--------------------+ 6 rows in set (0.00 sec) mysql> select name from t1 where name in (select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA); +--------+ | name | +--------+ | user04 | | user06 | +--------+ 2 rows in set (0.00 sec) mysql> \s -------------- bin/mysql Ver 14.14 Distrib 5.5.53, for linux2.6 (x86_64) using EditLine wrapper Connection id: 1 Current database: test Current user: root@localhost SSL: Not in use Current pager: more Using outfile: '' Using delimiter: ; Server version: 5.5.53-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql_ushastry.sock Uptime: 19 sec Threads: 1 Questions: 21 Slow queries: 0 Opens: 34 Flush tables: 1 Open tables: 27 Queries per second avg: 1.105 -------------- mysql> SHOW CREATE TABLE INFORMATION_SCHEMA.SCHEMATA\G *************************** 1. row *************************** Table: SCHEMATA Create Table: CREATE TEMPORARY TABLE `SCHEMATA` ( `CATALOG_NAME` varchar(512) NOT NULL DEFAULT '', `SCHEMA_NAME` varchar(64) NOT NULL DEFAULT '', `DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', `DEFAULT_COLLATION_NAME` varchar(32) NOT NULL DEFAULT '', `SQL_PATH` varchar(512) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8 1 row in set (0.00 sec) -- 5.6.34 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.34: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.34-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> DROP DATABASE IF EXISTS user04; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> DROP DATABASE IF EXISTS user06; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE DATABASE user04; Query OK, 1 row affected (0.00 sec) mysql> CREATE DATABASE user06; Query OK, 1 row affected (0.00 sec) mysql> mysql> use test; Database changed mysql> drop table if exists t1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table t1(id int, name varchar(64)); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(1,'user02'),(2,'ddd'),(3,'user04'),(4,'dbaaa'),(5,'user06'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------+--------+ | id | name | +------+--------+ | 1 | user02 | | 2 | ddd | | 3 | user04 | | 4 | dbaaa | | 5 | user06 | +------+--------+ 5 rows in set (0.00 sec) mysql> select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA; +--------------------+ | SCHEMA_NAME | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | user04 | | user06 | +--------------------+ 6 rows in set (0.00 sec) mysql> select name from t1 where name in (select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA); Empty set (0.00 sec) mysql> DESC INFORMATION_SCHEMA.SCHEMATA; +----------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------------+--------------+------+-----+---------+-------+ | CATALOG_NAME | varchar(512) | NO | | | | | SCHEMA_NAME | varchar(64) | NO | | | | | DEFAULT_CHARACTER_SET_NAME | varchar(32) | NO | | | | | DEFAULT_COLLATION_NAME | varchar(32) | NO | | | | | SQL_PATH | varchar(512) | YES | | NULL | | +----------------------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> \s -------------- bin/mysql Ver 14.14 Distrib 5.6.34, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 1 Current database: test Current user: root@localhost SSL: Not in use Current pager: more Using outfile: '' Using delimiter: ; Server version: 5.6.34-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql_ushastry.sock Uptime: 24 sec Threads: 1 Questions: 21 Slow queries: 0 Opens: 68 Flush tables: 1 Open tables: 61 Queries per second avg: 0.875 -------------- mysql> mysql> SHOW CREATE TABLE INFORMATION_SCHEMA.SCHEMATA\G *************************** 1. row *************************** Table: SCHEMATA Create Table: CREATE TEMPORARY TABLE `SCHEMATA` ( `CATALOG_NAME` varchar(512) NOT NULL DEFAULT '', `SCHEMA_NAME` varchar(64) NOT NULL DEFAULT '', `DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', `DEFAULT_COLLATION_NAME` varchar(32) NOT NULL DEFAULT '', `SQL_PATH` varchar(512) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8 1 row in set (0.00 sec) -- 5.7.16 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.16: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.16 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> DROP DATABASE IF EXISTS user04; Query OK, 0 rows affected (0.00 sec) mysql> DROP DATABASE IF EXISTS user06; Query OK, 0 rows affected (0.00 sec) mysql> CREATE DATABASE user04; Query OK, 1 row affected (0.00 sec) mysql> CREATE DATABASE user06; Query OK, 1 row affected (0.00 sec) mysql> DROP DATABASE IF EXISTS test; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE DATABASE test; Query OK, 1 row affected (0.00 sec) mysql> mysql> use test; Database changed mysql> drop table if exists t1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table t1(id int, name varchar(64)); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(1,'user02'),(2,'ddd'),(3,'user04'),(4,'dbaaa'),(5,'user06'); select * from t1; Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------+--------+ | id | name | +------+--------+ | 1 | user02 | | 2 | ddd | | 3 | user04 | | 4 | dbaaa | | 5 | user06 | +------+--------+ 5 rows in set (0.00 sec) mysql> select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA; +--------------------+ | SCHEMA_NAME | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | user04 | | user06 | +--------------------+ 7 rows in set (0.00 sec) mysql> select name from t1 where name in (select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA); Empty set (0.00 sec) mysql> \s -------------- bin/mysql Ver 14.14 Distrib 5.7.16, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 3 Current database: test Current user: root@localhost SSL: Not in use Current pager: more Using outfile: '' Using delimiter: ; Server version: 5.7.16 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql_ushastry.sock Uptime: 1 min 7 sec Threads: 1 Questions: 40 Slow queries: 0 Opens: 108 Flush tables: 1 Open tables: 101 Queries per second avg: 0.597 -------------- mysql> desc INFORMATION_SCHEMA.SCHEMATA; +----------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------------+--------------+------+-----+---------+-------+ | CATALOG_NAME | varchar(512) | NO | | | | | SCHEMA_NAME | varchar(64) | NO | | | | | DEFAULT_CHARACTER_SET_NAME | varchar(32) | NO | | | | | DEFAULT_COLLATION_NAME | varchar(32) | NO | | | | | SQL_PATH | varchar(512) | YES | | NULL | | +----------------------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> SHOW CREATE TABLE INFORMATION_SCHEMA.SCHEMATA\G *************************** 1. row *************************** Table: SCHEMATA Create Table: CREATE TEMPORARY TABLE `SCHEMATA` ( `CATALOG_NAME` varchar(512) NOT NULL DEFAULT '', `SCHEMA_NAME` varchar(64) NOT NULL DEFAULT '', `DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', `DEFAULT_COLLATION_NAME` varchar(32) NOT NULL DEFAULT '', `SQL_PATH` varchar(512) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8 1 row in set (0.00 sec)