The MySQL unknown column in field list error happens when you put a column name in your SQL script that can’t be found by MySQL.
For example, suppose you have a table named students with the following data:
+----+---------+---------+-------+--------+
| id | name | subject | score | gender |
+----+---------+---------+-------+--------+
| 1 | Sarah | Math | 9 | male |
| 2 | Natalia | Math | 8 | female |
| 3 | Christ | English | 5 | male |
+----+---------+---------+-------+--------+
When you try to insert into a column that doesn’t exist in the table, MySQL will throw the said error:
INSERT INTO students(student_name) VALUES (Michael);
-- ERROR 1054 (42S22): Unknown column 'student_name' in 'field list'
The error above is because there’s no student_name column in the students table.
Next, the error can also be triggered because you didn’t use quotes for the string values as follows:
INSERT INTO students(name) VALUES (Michael);
-- ERROR 1054 (42S22): Unknown column 'Michael' in 'field list'
The value Michael must be wrapped in quotations ('' or "") or MySQL will think you are trying to insert values from another column to the target column.
The error can also be triggered by other things that make MySQL think that your column doesn’t exist.
One example is that you may trigger the error when calling a variable without the @ symbol as shown below:
SET @myVar="Hello";
SELECT myVar;
-- ERROR 1054 (42S22): Unknown column 'myVar' in 'field list'
Another thing that could trigger the error is that you’re using backticks to wrap literal values, as in the following UPDATE statement:
UPDATE students SET subject = `English` WHERE id = 1;
-- ERROR 1054 (42S22): Unknown column 'English' in 'field list'
The English value for the subject field above should be wrapped by quotations (single or double) instead of backticks.
The error can also be triggered when you have a SELECT query that has different tables between the SELECT clause and the FROM clause as shown below:
SELECT students.name FROM cities;
-- ERROR 1054 (42S22): Unknown column 'students.name' in 'field list'
This is because you are trying to query a column owned by students table from the cities table, which doesn’t match.
Finally, the error can also be caused by invisible characters lurking in your script that can’t be seen when you copy and paste it from other sources.
The only way to remove invisible characters is by rewriting the statements manually.
Summary
The tutorial above has listed the most common cause for MySQL unknown column in field list error.
There are many variations of SQL statements that can cause this error, so this error doesn’t really help you find the cause without understanding how MySQL syntax works.
I hope this tutorial has given you some guidelines on fixing the error. Good luck! 👍
