包含多层子查询的视图的创建和查询必须要指定 use database;
数据库版本：5.7.25-log
----------------脚本准备----------------
创建database：
create database ha_test;
create database test2;
创建表语句：
CREATE TABLE t1 (
id int(11) NOT NULL,
col1 varchar(64) DEFAULT NULL,
col2 varchar(64) DEFAULT NULL,
col3 varchar(64) DEFAULT NULL,
PRIMARY KEY (id),
KEY col1 (col1,col2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE t2 (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(30) DEFAULT NULL,
age int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;
CREATE TABLE ha_t (
id int(11) DEFAULT NULL,
name varchar(30) DEFAULT ‘xiaoming’,
age varchar(30) DEFAULT ‘99’,
FULLTEXT KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
视图语句：
CREATE VIEW test2.v3 AS SELECT
t.id
FROM
(
SELECT
id
FROM
ha_test.t t1 UNION ALL
SELECT
id
FROM
ha_test.t2
WHERE
( NOT ( EXISTS ( SELECT 1 FROM ha_test.ha_t t3 WHERE ( t3.id = t2.id ) ) ) )
) t
问题1：直接登陆mysql数据库，不使用use database,再创建包含多层子查询的视图(视图名db_name.view_name)时会出现ERROR 1142 (42000): ANY command denied to user ‘root’@‘localhost’ for table ''
现象：
[mysql@orcl ~]$ mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright © 2000, 2019, 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> CREATE VIEW test2.v3 AS SELECT
-> t.id
-> FROM
-> (
-> SELECT
-> id
-> FROM
-> ha_test.t t1 UNION ALL
-> SELECT
-> id
-> FROM
-> ha_test.t2
-> WHERE
-> ( NOT ( EXISTS ( SELECT 1 FROM ha_test.ha_t t3 WHERE ( t3.id = t2.id ) ) ) )
-> ) t;
ERROR 1142 (42000): ANY command denied to user ‘root’@‘localhost’ for table ''
解决方法：先use database 再 创建视图
如下：
mysql> use test2;
Database changed
mysql> CREATE VIEW test2.v3 AS SELECT
-> t.id
-> FROM
-> (
-> SELECT
-> id
-> FROM
-> ha_test.t t1 UNION ALL
-> SELECT
-> id
-> FROM
-> ha_test.t2
-> WHERE
-> ( NOT ( EXISTS ( SELECT 1 FROM ha_test.ha_t t3 WHERE ( t3.id = t2.id ) ) ) )
-> ) t;
Query OK, 0 rows affected (0.01 sec)
问题2：直接登陆mysql数据库，不适用use database,在查询包含多层子查询的视图(视图名db_name.view_name)时会出现ERROR 1046 (3D000): No database selected
现象：
[mysql@orcl ~]$ mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright © 2000, 2019, 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> select * from test2.v3;
ERROR 1046 (3D000): No database selected
解决方法：先use database 再查询视图
如下：
mysql> use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test2.v3;
±----+
| id |
±----+
| 5 |
| 3 |
±----+
说明：
以上问题时开发人员反馈给我的，我们的开发人员一直保留着oracle的操作习惯，创建视图或者查询视图时不会去先使用use database,
而更愿意使用 db_name.view_name的方式去创建或者查询视图，因此才出现上面类似的问题，查询mysql官网，也已经发现了相应的Bug #91122
上面的问题在mysql8.0中已修复，但是在mysql5.7中只能采用先use database ,在查询或者创建视图
