How to Fix Error Code 1054 Unknown Column in MySQL

As a free, open-source relational database that can cover a lot of use cases, from dynamic web-based discussion boards and blogs to enterprise resource planning software, MySQL has attracted a lot of users and developers over the years.

As its name implies, MySQL uses SQL (Structured Query Language) as its language, with its extensions to the SQL standard to accommodate its unique features.

For users who are not technically inclined, there are also various graphical tools to manage MySQL databases, ranging from enterprise-grade MySQL Workbench to the popular phpMyAdmin.

However, from time to time you still need to use SQL directly to manipulate your MySQL database. The SQL language is versatile and also used in other database management systems, so users familiar with other systems such as Microsoft SQL Server or Oracle PL/SQL will not have any issue operating MySQL.

That said, the error messages are different as MySQL has its own dialect of SQL. In this article, we will talk about Error Code 1054 on MySQL and how to fix it.

When you create a query, be it manipulation (INSERT INTO or UPDATE) or definition (CREATE TABLE), you might encounter Error Code 1054.

This error code will be shown if the column you mentioned in the query does not exist in the database, or if you try to insert the wrong type of data in your SQL statement.

Fixing Error Code 1054 Issue in MySQL

To fix the query, the first thing to do is to check whether the column you specified in the query exists in the table. This can be done by using the query EXPLAIN table_name;

After issuing the query, the MySQL console will display the table structure, and from there you can fix your query so that your query only contains the right columns.

Using EXPLAIN, you will also see the data types of the columns in a certain table. If, for example, you inserted alphanumeric characters to a column with VARCHAR data type, your query will return the 1054 error.

Therefore, it is a good practice to use EXPLAIN before writing any INSERT or UPDATE queries to avoid Error Code 1054.

For example you a table named “wp_users” and you want to get the information about the table. You can simply type:

EXPLAIN wp_users;

It will then output something like this:

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| user_id      | int         | NO   |     |         |       |
| display_name | varchar(50) | NO   |     |         |       |
| user_age     | int         | YES  |     | NULL    |       |
| user_desc    | text        | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

And if you issue this query:

SELECT username FROM wp_users;

You will see the following error:

ERROR 1054 (42S22): Unknown column 'username' in 'field list'

The solution is to change the column name to the right one by referring to the EXPLAIN output:

SELECT user_id FROM wp_users;

Then, the query will return the data inside the user_name column properly.

Since you’re here, you want also want to read our guide on how to reset MySQL root password on Debian/Ubuntu & CentOS.

Solving Error Code 1054 When Using INSERT Statement

Similarly, when you issue this command to insert a new record in the table:

mysql> INSERT INTO wp_users(user_id, display_name)
-> VALUES ("whoami”, “Who Am I”);
ERROR 1054 (42S22): Unknown column 'user_id' in 'field list'

It will fail because user_id is set to accept integers only, not characters. Refer to the EXPLAIN output and correct the query to reflect the right column and data type.

mysql> INSERT INTO wp_users(user_id, display_name)
-> VALUES ("69”, “Who Am I”);

The corrected query will then change the display name of users with the ID “69” to “Who Am I”. The query will no longer return Error 1054 because the column name and data type have been aligned according to the table.

Conclusion

The main thing to prevent this issue in MySQL is always to make sure you know your table well. You can get to know your column well by using the EXPLAIN statement or simply view your table using phpMyAdmin or other similar tools.

Node35 Editorial Team

We are a bunch of people who are still continue to learn Linux servers. Only high passion keeps pushing us to learn everything.

READ:  How To Reset Root Password on MariaDB v10+