MySQL 打开视图 1449_Mysql查询视图：ERROR 1449 (HY000)解决办法

问题重现

前几天因为有人删除了数据库中的记录，今天关闭了数据库的远程访问功能，今天接到开发报告，说出现 The user specified as a definer (‘air’@'%’) does not exist错误，他们定位是一张视图不能访问。利用实验重现了他们的情况

原因分析

因为创建视图使用的是xff@%用户(目前已经不存在)，然后登录用户使用的是xff@localhost用户，导致mysql认为现在的用户无权限访问该视图，解决方法就是在当前用户下重建该视图

我使用的代码

代码如下

[root@ECP-UC-DB1 ~]# mysql -uxff -pxifenfei

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 8846

Server version: 5.5.14-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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 user,host from mysql.user;

+------+---------------+

| user | host          |

+------+---------------+

| xff  | %             |

| root | 127.0.0.1     |

| repl | 192.168.11.10 |

| root | ::1           |

|      | ECP-UC-DB1    |

| root | ECP-UC-DB1    |

| root | localhost     |

+------+---------------+

7 rows in set (0.08 sec)

mysql> use xifenfei;

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> create view v_users as select * from wp_users;

Query OK, 0 rows affected (0.14 sec)

mysql> select count(*) from xifenfei.v_users;

+----------+

| count(*) |

+----------+

|        2 |

+----------+

1 row in set (0.03 sec)

mysql> update mysql.user set host='localhost' where user='xff' and host='%';

Query OK, 1 row affected (0.05 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.12 sec)

mysql> exit

Bye

[root@ECP-UC-DB1 ~]# mysql -uxff -pxifenfei

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 8847

Server version: 5.5.14-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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> use xff;

ERROR 1049 (42000): Unknown database 'xff'

mysql> use xifenfei;

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 v_users ;

ERROR 1449 (HY000): The user specified as a definer ('xff'@'%') does not exist

2、解决方法

代码如下

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| xifenfei           |

+--------------------+

5 rows in set (0.00 sec)

mysql> use information_schema;

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> desc VIEWS;

+----------------------+--------------+------+-----+---------+-------+

| Field                | Type         | Null | Key | Default | Extra |

+----------------------+--------------+------+-----+---------+-------+

| TABLE_CATALOG        | varchar(512) | NO   |     |         |       |

| TABLE_SCHEMA         | varchar(64)  | NO   |     |         |       |

| TABLE_NAME           | varchar(64)  | NO   |     |         |       |

| VIEW_DEFINITION      | longtext     | NO   |     | NULL    |       |

| CHECK_OPTION         | varchar(8)   | NO   |     |         |       |

| IS_UPDATABLE         | varchar(3)   | NO   |     |         |       |

| DEFINER              | varchar(77)  | NO   |     |         |       |

| SECURITY_TYPE        | varchar(7)   | NO   |     |         |       |

| CHARACTER_SET_CLIENT | varchar(32)  | NO   |     |         |       |

| COLLATION_CONNECTION | varchar(32)  | NO   |     |         |       |

+----------------------+--------------+------+-----+---------+-------+

10 rows in set (0.02 sec)

mysql> select TABLE_SCHEMA,TABLE_NAME,DEFINER from views;

+--------------+------------+---------+

| TABLE_SCHEMA | TABLE_NAME | DEFINER |

+--------------+------------+---------+

| xifenfei     | v_users    | xff@%   |

+--------------+------------+---------+

1 row in set (0.16 sec)

mysql> create or replace view v_users as select * from wp_users;

ERROR 1044 (42000): Access denied for user 'xff'@'localhost' to database 'information_schema'

mysql> create or replace view xifenfei.v_users as select * from xifenfei.wp_users;

Query OK, 0 rows affected (0.02 sec)

mysql> select TABLE_SCHEMA,TABLE_NAME,DEFINER from views;

+--------------+------------+---------------+

| TABLE_SCHEMA | TABLE_NAME | DEFINER       |

+--------------+------------+---------------+

| xifenfei     | v_users    | xff@localhost |

+--------------+------------+---------------+

1 row in set (0.01 sec)

mysql> select count(*) from xifenfei.v_users;

+----------+

| count(*) |

+----------+

|        2 |

+----------+

1 row in set (0.03 sec)

1.注意事项

创建视图存在如下注意事项：

(1) 运行创建视图的语句需要用户具有创建视图(CRATE VIEW)的权限，若加了[OR REPLACE]时，还需要用户具有删除视图(DROP VIEW)的权限；

(2) SELECT语句不能包含FROM子句中的子查询；

(3) SELECT语句不能引用系统或用户变量；

(4) SELECT语句不能引用预处理语句参数；

(5) 在存储子程序内，定义不能引用子程序参数或局部变量；

(6) 在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句；

(7) 在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图；

(8) 在视图定义中命名的表必须已存在；

(9) 不能将触发程序与视图关联在一起；

(10) 在视图定义中允许使用ORDER BY，但是，如果从特定视图进行了选择，而该视图使用了具有自己ORDER BY的语句，它将被忽略。

补充一下mysql视图基本知识

创建视图——CREATE VIEW

1.语法

代码如下

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

通过该语句可以创建视图，若给定了[OR REPLACE]，则表示当已具有同名的视图时，将覆盖原视图。select_statement是一个查询语句，这个查询语句可从表或其它的视图中查询。视图属于数据库，因此需要指定数据库的名称，若未指定时，表示在当前的数据库创建新视图。

表和数据库共享数据库中相同的名称空间，因此，数据库不能包含相同名称的表和视图，并且，视图的列名也不能重复。

1.使用举例

Eg. 本例创建一个产品表(product)和一个购买记录表(purchase)，再通过视图purchase_detail查询出购买的详细信息。

代码如下

CREATE TABLE product

(

product_id INT NOT NULL,

name VARCHAR(50) NOT NULL,

price DOUBLE NOT NULL

);

INSERT INTO product VALUES(1, 'apple ', 5.5);

CREATE TABLE purchase

(

id INT NOT NULL,

product_id INT NOT NULL,

qty INT NOT NULL DEFAULT 0,

gen_time DATETIME NOT NULL

);

INSERT INTO purchase VALUES(1, 1, 10, NOW());

CREATE VIEW purchase_detail AS SELECT product.name as name, product .price as price, purchase.qty as qty, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;

创建成功后，输入：SELECT * FROM purchase_detail;

运行效果如下：

代码如下

+-------+-------+-----+-------------+

| name | price | qty | total_value |

+-------+-------+-----+-------------+

| apple | 5.5 | 10 | 55 |

+-------+-------+-----+-------------+

1 row in set (0.01 sec)