[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> 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)
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)