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
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.
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
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:
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
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.
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.