Mysql查詢視圖：ERROR 1449 (HY000)解決辦法

本文章來給大家介紹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)