[Solved] MySQL ERROR 1054 (42S22): Unknown column ‘i2goods.t_ebook_data.fbookid’ in ‘field list’

1.1.1 Phenomenon

When importing data, the following error occurs.

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

1.1.2 Reason

The internet usually says that there is indeed a column not found, and after checking the imported SQL file, the error is a statement to create a view, the SQL statement is as follows.

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

 

When I opened the database with the tool and checked the exported table t_ebook_data, there was indeed the field fbookid, so the error message felt baffling.

Later, after careful analysis, I found that there is a prefix in front of the table name: ‘i2goods’, which is actually the name of the database, so I suspected that this is not the cause, and deleted all the ‘i2goods’ prefixes in the statement on the table, and the result was fine. The result was fine.

1.1.3 Solution

Remove all the database name prefixes from the create view statement, then perform the import operation, and it works.

In addition, to solve the following Got error: 1449 problem, after raising the privileges of the root user, you don’t need to remove the database name prefix in the above way, because when you look at the exported SQL file, all SQL statements in it have no database name prefix, so the import is fine.

Similar Posts: