When you execute a MySQL statement, you may sometimes encounter ERROR 1054 as shown below:
mysql> SELECT user_name FROM users;
ERROR 1054 (42S22): Unknown column 'user_name' in 'field list'
The ERROR 1054 in MySQL occurs because MySQL can’t find the column or field you specified in your statement.
This error can happen when you execute any valid MySQL statements like a SELECT
, INSERT
, UPDATE
, or ALTER TABLE
statement.
This tutorial will help you fix the error by adjusting your SQL statements.
Let’s start with the SELECT
statement.
Fix ERROR 1054 on a SELECT statement
To fix the error in your SELECT
statement, you need to make sure that the column(s) you specified in your SQL statement actually exists in your database table.
Because the error above says that user_name
column is unknown, let’s check the users
table and see if the column exists or not.
To help you check the table in question, you can use the DESCRIBE
or EXPLAIN
statement to show your table information.
The example below shows the output of EXPLAIN
statement for the users
table:
mysql> EXPLAIN users;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| username | varchar(25) | NO | | | |
| display_name | varchar(50) | NO | | | |
| age | int | YES | | NULL | |
| comments | text | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
From the result above, you can see that the users
table has no user_name
field (column)
Instead, it has the username
column without the underscore.
Knowing this, I can adjust my previous SQL query to fix the error:
SELECT username FROM users;
That should fix the error and your SQL query should show the result set.
Fix ERROR 1054 on an INSERT statement
When you specify column names in an INSERT
statement, then the error can be triggered on an INSERT
statement because of a wrong column name, just like in the SELECT
statement.
First, you need to check that you have the right column names in your statement.
Once you are sure, the next step is to look at the VALUES()
you specified in the statement.
For example, when I ran the following statement, I triggered the 1054 error:
mysql> INSERT INTO users(username, display_name)
-> VALUES ("jackolantern", Jack);
ERROR 1054 (42S22): Unknown column 'Jack' in 'field list'
The column names above are correct, and the error itself comes from the last entry in the VALUES()
function.
The display_name
column is of VARCHAR
type, so MySQL expects you to insert a VARCHAR
value into the column.
But Jack
is not a VARCHAR
value because it’s not enclosed in a quotation mark. MySQL considers the value to be a column name.
To fix the error above, simply add a quotation mark around the value. You can use both single quotes or double quotes as shown below:
INSERT INTO users(username, display_name)
VALUES ("jackolantern", 'Jack');
Now the INSERT
statement should run without any error.
Fix ERROR 1054 on an UPDATE statement
To fix the 1054 error caused by an UPDATE
statement, you need to look into the SET
and WHERE
clauses of your statement and make sure that the column names are all correct.
You can look at the error message that MySQL gave you to identify where the error is happening.
For example, the following SQL statement:
UPDATE users
SET username = "jackfrost", display_name = "Jack Frost"
WHERE user_name = "jackolantern";
Produces the following error:
ERROR 1054 (42S22): Unknown column 'user_name' in 'where clause'
The error clearly points toward the user_name
column in the WHERE
clause, so you only need to change that.
If the error points toward the field_list
as shown below:
ERROR 1054 (42S22): Unknown column 'displayname' in 'field list'
Then you need to check on the SET
statement and make sure that:
- You have the right column names
- Any
string
type values are enclosed in a quotation mark
You can also check on the table name that you specified in the UPDATE
statement and make sure that you’re operating on the right table.
Next, let’s look at how to fix the error on an ALTER TABLE
statement
Fix ERROR 1054 on an ALTER TABLE statement
The error 1054 can also happen on an ALTER TABLE
statement.
For example, the following statement tries to rename the displayname
column to realname
:
ALTER TABLE users
RENAME COLUMN displayname TO realname;
Because there’s no displayname
column name in the table, MySQL will respond with the ERROR 1054 message.
Conclusion
In short, ERROR 1054 means that MySQL can’t find the column name that you specified in your SQL statements.
It doesn’t matter if you’re writing an INSERT
, SELECT
, or UPDATE
statement.
There are only two things you need to check to fix the error:
- Make sure you’ve specified the right column name in your statement
- Make sure that any value of
string
type in your statement is surrounded by a quotation mark
You can check on your table structure using the DESCRIBE
or EXPLAIN
statement to help you match the column name and type with your statement.
And that’s how you fix the MySQL ERROR 1054 caused by your SQL statements.
I hope this tutorial has been useful for you 🙏