我显然有一个简单的问题。作为文档,我可以检查数据库上是否存在表或视图。在我的应用程序,我需要检查如果两者都已经存在于数据库返回自定义错误。问题是,我可以检查是否有一个表,但不是视图。例:
**Working case**
$checkTable = $this->db->query("SELECT * FROM sqlite_master WHERE name ='$table' and type='table'", PDO::FETCH_ASSOC);
**Not working case**
$checkTable = $this->db->query("SELECT * FROM sqlite_master WHERE name ='$table' and type='table' OR type='views'", PDO::FETCH_ASSOC);
**Not working case**
$checkTable = $this->db->query("SELECT * FROM sqlite_master WHERE name ='$table' and type='views'", PDO::FETCH_ASSOC);
奇怪的是,如果我在视图上运行查询,这将返回结果:
**Working case**
$checkTable = $this->db->query("SELECT * FROM VIEWSpeople WHERE name ='$person'", PDO::FETCH_ASSOC);
查询sqlite_master的视图:
**Working case**
$checkTable = $this->db->query("SELECT * FROM sqlite_master WHERE name ='$table'", PDO::FETCH_ASSOC);
问题:为什么查询不能识别为type='views'的视图?感谢您的宝贵时间。
视图类型不是views
,而是view
。
此外,您应该使用括号来确保表达式以正确的顺序求值:
... WHERE name ='$table' AND (type='table' OR type='view')
或者使用IN:
... WHERE name ='$table' AND type IN ('table', 'view')