查看视图报错:ERROR 1356 (HY000): View 'test.worker_view1' references invalid table(s) or

来源:csdn博客 分类: 文章浏览史 发布时间:2020-12-24 16:30:11 最后更新:2020-12-24 浏览:150
转载声明:
本文为摘录自“csdn博客”,版权归原作者所有。
温馨提示:
为了更好的体验,请点击原文链接进行浏览
摘录时间:
2020-12-24 16:30:11

ERROR 1356 (HY000): View ‘test.worker_view1’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
原因是我发现视图依赖的表有个字段错误brithday写错为birthday。我就将他改了,就报错啦。

MariaDB [test]> desc worker;
±------------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±------------±-----±----±--------±------+
| num | int(10) | NO | PRI | NULL | |
| d_id | int(4) | YES | MUL | NULL | |
| name | varchar(20) | NO | | NULL | |
| sex | varchar(4) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
| homeaddress | varchar(50) | YES | | NULL | |
±------------±------------±-----±----±--------±------+
6 rows in set (0.00 sec)

MariaDB [test]> create algorithm=merge view worker_view1(name,department,sex,age,address)
-> as select name,department.d_name,sex,2013-brithday,address
-> from worker,department where worker.d_id=department.d_id
-> with local check option;
ERROR 1054 (42S22): Unknown column ‘brithday’ in ‘field list’
MariaDB [test]> create algorithm=merge view worker_view1(name,department,sex,age,address) as select name,department.d_name,sex,2013-birthday,address from worker,department where worker.d_id=department.d_id with local check option;
Query OK, 0 rows affected (0.06 sec)

MariaDB [test]> desc worker_view1;
±-----------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-----------±------------±-----±----±--------±------+
| name | varchar(20) | NO | | NULL | |
| department | varchar(20) | NO | | NULL | |
| sex | varchar(4) | NO | | NULL | |
| age | bigint(21) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
±-----------±------------±-----±----±--------±------+
5 rows in set (0.04 sec)

MariaDB [test]> alter table worker modify birthday brithday datetime;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘brithday datetime’ at line 1
MariaDB [test]> alter table worker change birthday brithday datetime;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> desc worker_view1;
ERROR 1356 (HY000): View ‘test.worker_view1’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
MariaDB [test]> desc worker_view1;
ERROR 1356 (HY000): View ‘test.worker_view1’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
MariaDB [test]> desc worker;
±------------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±------------±-----±----±--------±------+
| num | int(10) | NO | PRI | NULL | |
| d_id | int(4) | YES | MUL | NULL | |
| name | varchar(20) | NO | | NULL | |
| sex | varchar(4) | NO | | NULL | |
| brithday | datetime | YES | | NULL | |
| homeaddress | varchar(50) | YES | | NULL | |
±------------±------------±-----±----±--------±------+
6 rows in set (0.01 sec)

MariaDB [test]> alter table worker change brithday birthday datetime;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> desc worker_view1;
±-----------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-----------±------------±-----±----±--------±------+
| name | varchar(20) | NO | | NULL | |
| department | varchar(20) | NO | | NULL | |
| sex | varchar(4) | NO | | NULL | |
| age | bigint(21) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
±-----------±------------±-----±----±--------±------+
5 rows in set (0.00 sec)

php技术微信