MySQL - ERROR 1054 (42S22)

1.1.1  现象

导入数据时，出现如下错误：

ERROR 1054 (42S22) at line 734: Unknown column 'i2goods.t_ebook_data.fbookid' in 'field list'

1.1.2  原因

网上通常都说确实是有列找不到，查看了一下导入的SQL文件，出错的地方是个创建视图的语句，SQL语句如下：

VIEW `v_ebook_data` AS select   `t_ebook_data`.`fbookid` AS `fbookid`,   `t_ebook_data`.`fperiodid` AS `fperiodid`,   1 AS `fdevicetype`,   `t_ebook_data`.`ftitlephoto` AS `ftitlephoto`,   `t_ebook_data`.`fformat` AS `fformat`,   `t_ebook_data`.`ffilepath` AS `ffilepath`,   `t_ebook_data`.`ffilesize` AS `ffilesize`,   `t_ebook_data`.`fdataversion` AS `fdataversion`,   `t_ebook_data`.`fdataid` AS `fdataid` from `t_ebook_data` where (`t_ebook_data`.`fdevicetype` = 1) union select `t1`.`fbookid` AS `fbookid`,`t1`.`fperiodid` AS `fperiodid`,1 AS `fdevicetype`,`t1`.`ftitlephoto` AS `ftitlephoto`,`t1`.`fformat` AS `fformat`,`t1`.`ffilepath` AS `ffilepath`,`t1`.`ffilesize` AS `ffilesize`,`t1`.`fdataversion` AS `fdataversion`,`t1`.`fdataid` AS `fdataid` from `t_ebook_data` `t1` where ((`t1`.`fdevicetype` = 0) and (not(exists(select 1 from `t_ebook_data` `t2` where ((`t2`.`fdevicetype` = 1) and (`t1`.`fbookid` = `t2`.`fbookid`) and (`t1`.`fperiodid` = `t2`.`fperiodid`)))))) union select `t_ebook_data`.`fbookid` AS `fbookid`,`t_ebook_data`.`fperiodid` AS `fperiodid`,2 AS `fdevicetype`,`t_ebook_data`.`ftitlephoto` AS `ftitlephoto`,`t_ebook_data`.`fformat` AS `fformat`,`t_ebook_data`.`ffilepath` AS `ffilepath`,`t_ebook_data`.`ffilesize` AS `ffilesize`,`t_ebook_data`.`fdataversion` AS `fdataversion`,`t_ebook_data`.`fdataid` AS `fdataid` from `t_ebook_data` where (`t_ebook_data`.`fdevicetype` = 2) union select `t1`.`fbookid` AS `fbookid`,`t1`.`fperiodid` AS `fperiodid`,2 AS `fdevicetype`,`t1`.`ftitlephoto` AS `ftitlephoto`,`t1`.`fformat` AS `fformat`,`t1`.`ffilepath` AS `ffilepath`,`t1`.`ffilesize` AS `ffilesize`,`t1`.`fdataversion` AS `fdataversion`,`t1`.`fdataid` AS `fdataid` from `t_ebook_data` `t1` where ((`t1`.`fdevicetype` = 0) and (not(exists(select 1 from `t_ebook_data` `t2` where ((`t2`.`fdevicetype` = 2) and (`t1`.`fbookid` = `t2`.`fbookid`) and (`t1`.`fperiodid` = `t2`.`fperiodid`)))))) union select `t_ebook_data`.`fbookid` AS `fbookid`,`t_ebook_data`.`fperiodid` AS `fperiodid`,3 AS `fdevicetype`,`t_ebook_data`.`ftitlephoto` AS `ftitlephoto`,`t_ebook_data`.`fformat` AS `fformat`,`t_ebook_data`.`ffilepath` AS `ffilepath`,`t_ebook_data`.`ffilesize` AS `ffilesize`,`t_ebook_data`.`fdataversion` AS `fdataversion`,`t_ebook_data`.`fdataid` AS `fdataid` from `t_ebook_data` where (`t_ebook_data`.`fdevicetype` = 3) union select `t1`.`fbookid` AS `fbookid`,`t1`.`fperiodid` AS `fperiodid`,3 AS `fdevicetype`,`t1`.`ftitlephoto` AS `ftitlephoto`,`t1`.`fformat` AS `fformat`,`t1`.`ffilepath` AS `ffilepath`,`t1`.`ffilesize` AS `ffilesize`,`t1`.`fdataversion` AS `fdataversion`,`t1`.`fdataid` AS `fdataid` from `t_ebook_data` `t1` where ((`t1`.`fdevicetype` = 0) and (not(exists(select 1 from `t_ebook_data` `t2` where ((`t2`.`fdevicetype` = 3) and (`t1`.`fbookid` = `t2`.`fbookid`) and (`t1`.`fperiodid` = `t2`.`fperiodid`))))))

 

用工具打开数据库，查看被导出的表t_ebook_data，确实有fbookid这个字段，所以错误提示看得感觉莫名其妙。

后来再仔细分析，发现表名前面还有个前缀：’i2goods’，这个实际上是数据库名，怀疑是不是这个导致，把上表的语句中所有’i2goods’前缀全部删除，结果就没问题了。

1.1.3  解决

将创建视图的语句中，表示数据库名称的前缀全部去掉，再执行导入操作，就可以了。

另外，解决下面Got error: 1449的问题时，将root用户权限提升后，就不用上面的方法去掉数据库名称前缀，因为查看导出的SQL文件，里面所有SQL语句都没有了数据库名称前缀，因此导入也没问题了。

转载于:https://my.oschina.net/tywali/blog/679392