oracle视图转换为mysql视图_oracle迁移改造成mysql遇到的那些坑--包含多层子查询的视图的创建和查询必须要指定 use database;...

包含多层子查询的视图的创建和查询必须要指定 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 ,在查询或者创建视图