Mysql
mysql更新查詢錯誤程式碼1054欄位列表中的未知列
Error: Code 1054. Unknown column 'U2.id_naslov' in 'field list'在 MySQL Workbench 中的這個簡單查詢中被拋出：UPDATE krneki_1 AS U1, krneki_2 AS U2 SET U1.id_naslov = U2.id_naslov WHERE (U2.id_zaposlen = U1.id_naslovi_zaposleni)我在網上搜尋並閱讀了其他文章，但沒有任何幫助……
我想這是一個微不足道的解決方案，但我只是看不到它。
TSQL（sql server）上從未出現過這種錯誤。
表 krneki_2 是由 Mysql 工作台通過數據導入（創建新表）創建的，後來發生此錯誤時，我還將數字欄位更改為 smallint，只是為了看看它是否有幫助……但是……沒有。
結果
SHOW CREATE TABLE krneki_2：Table: krneki_2 Create Table: CREATE TABLE `krneki_2` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `id_naslov` smallint(6) NOT NULL, `id_zaposlen` smallint(6) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=204 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)結果
SHOW CREATE TABLE krneki_1：Table: krneki_1 Create Table: CREATE TABLE `krneki_1` ( `id_naslovi_zaposleni` smallint(6) NOT NULL AUTO_INCREMENT, `id_naslov` smallint(6) DEFAULT NULL, `id_zaposleni` smallint(6) DEFAULT NULL, `id_aktiven` tinyint(4) DEFAULT '0', `cas_vnosa` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id_naslovi_zaposleni`) ) ENGINE=InnoDB AUTO_INCREMENT=256 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)結果來自
information_schema，特別是來自評論中建議的此查詢：select table_catalog, table_schema, table_name, column_name, ordinal_position from information_schema.columns where table_name like '%krneki_1%' and column_name like '%naslov%' ;結果
krneki_1和naslov：+---------------+--------------+-------------+----------------------+------------------+ | table_catalog | table_schema | table_name | column_name | ordinal_position | +---------------+--------------+-------------+----------------------+------------------+ | def | hq_db | krneki_1 | id_naslovi_zaposleni | 1 | | def | hq_db | krneki_1 | id_naslov | 2 | +---------------+--------------+-------------+----------------------+------------------+ 2 rows in set (0.00 sec)結果
krneki_2和naslov：+---------------+--------------+-------------+--------------+------------------+ | table_catalog | table_schema | table_name | column_name | ordinal_position | +---------------+--------------+-------------+--------------+------------------+ | def | hq_db | krneki_2 | id_naslov | 2 | +---------------+--------------+-------------+--------------+------------------+ 1 row in set (0.00 sec)結果
krneki_2和zaposlen：+---------------+--------------+-------------+--------------+------------------+ | table_catalog | table_schema | table_name | column_name | ordinal_position | +---------------+--------------+-------------+--------------+------------------+ | def | hq_db | krneki_2 | id_zaposlen | 3 | +---------------+--------------+-------------+--------------+------------------+ 1 row in set (0.00 sec)進一步探勘，如建議的那樣：
select table_catalog, table_schema, table_name, column_name, ordinal_position, char_length(column_name) as cl, length(column_name) as l from information_schema.columns where table_name = 'krneki_2' ;結果
krneki_2：+-------------+------------+----------+-----------+----------------+---+---+-------------+ |table_catalog|table_schema|table_name|column_name|ordinal_position| cl| l | column_type | +-------------+------------+----------+-----------+----------------+---+---+-------------+ | def | hq_db | krneki_2 |id | 1 | 2| 2| smallint(6) | | def | hq_db | krneki_2 |id_naslov | 2 | 10| 12| smallint(6) | | def | hq_db | krneki_2 |id_zaposlen| 3 | 11| 11| smallint(6) | +-------------+------------+----------+-----------+----------------+---+---+-------------+ 3 rows in set (0.00 sec)結果
krneki_1：+-------------+------------+----------+--------------------+----------------+--+--+-----------+ |table_catalog|table_schema|table_name| column_name |ordinal_position|cl| l|column_type| +-------------+------------+----------+--------------------+----------------+--+--+-----------+ | def | hq_db | krneki_1 |id_naslovi_zaposleni| 1 |20|20|smallint(6)| | def | hq_db | krneki_1 |id_naslov | 2 | 9| 9|smallint(6)| | def | hq_db | krneki_1 |id_zaposleni | 3 |12|12|smallint(6)| | def | hq_db | krneki_1 |id_aktiven | 4 |10|10|tinyint(4) | | def | hq_db | krneki_1 |cas_vnosa | 5 | 9| 9|datetime | +-------------+------------+----------+--------------------+----------------+--+--+-----------+ 5 rows in set (0.00 sec)帶十六進制的 krneki_2：
+-------------+------------+----------+-----------+----------------+--+--+-------------------------+ |table_catalog|table_schema|table_name|column_name|ordinal_position|cl|l | hex | +-------------+------------+----------+-----------+----------------+--+--+-------------------------+ | def | hq_db | krneki_2 |id | 1 | 2| 2|6964 | | def | hq_db | krneki_2 |id_naslov | 2 |10|12|EFBBBF69645F6E61736C6F76 | | def | hq_db | krneki_2 |id_zaposlen| 3 |11|11|69645F7A61706F736C656E | +-------------+------------+----------+-----------+----------------+--+--+-------------------------+ 3 rows in set (0.00 sec)帶十六進制的 krneki_1：
+-------------+------------+----------+--------------------+----------------+--+--+----------------------------------------+ |table_catalog|table_schema|table_name|column_name |ordinal_position|cl| l|hex | +-------------+------------+----------+--------------------+----------------+--+--+----------------------------------------+ | def | hq_db | krneki_1 |id_naslovi_zaposleni| 1 |20|20|69645F6E61736C6F76695F7A61706F736C656E69| | def | hq_db | krneki_1 |id_naslov | 2 | 9| 9|69645F6E61736C6F76 | | def | hq_db | krneki_1 |id_zaposleni | 3 |12|12|69645F7A61706F736C656E69 | | def | hq_db | krneki_1 |id_aktiven | 4 |10|10|69645F616B746976656E | | def | hq_db | krneki_1 |cas_vnosa | 5 | 9| 9|6361735F766E6F7361 | +-------------+------------+----------+--------------------+----------------+--+--+----------------------------------------+ 5 rows in set (0.00 sec)
錯誤資訊非常清楚。該表
krneki_2沒有名為 的列id_naslov。除非系統表中存在某些損壞或錯誤，否則毫無疑問。所以我們必須排除出現這種情況的幾種可能性：
CREATE TABLE語句和之間存在不匹配UPDATE：CREATE TABLE ` krneki_1` ... CREATE TABLE ` krneki_2` ... UPDATE krneki_1 AS U1, krneki_2 AS U2 ...不匹配是名稱開頭的空格。
這應該給出*“表’krneki_1’不存在”*的錯誤，所以我有根據的猜測是你有兩個版本的表
krneki_1，沒有空格的版本沒有id_naslov列。我們消除了這種可能性，這是來自 OP 的複制粘貼錯誤。
CREATE TABLE和中的列名UPDATE不相同。它們可能看起來相同，但可能存在不可列印的字元，或者它們可能具有看起來相同但程式碼點不同的 Unicode 字元。要找出答案，我們可以使用以下查詢：select table_catalog, table_schema, table_name, column_name, ordinal_position, char_length(column_name) as cl, length(column_name) as l, hex(column_name) as hex from information_schema.columns where table_name = 'krneki_2' ;這揭示了差異（從輸出中刪除了不需要的列）：
+------------+-------------+------------------+----+----+ | table_name | column_name | ordinal_position | cl | l | +------------+-------------+------------------+----+----+ | krneki_2 | id | 1 | 2 | 2 | | krneki_2 | id_naslov | 2 | 10 | 12 | -- !!! -- | krneki_2 | id_zaposlen | 3 | 11 | 11 | +------------+-------------+------------------+----+----+注意 12 大於 10 有問題！這意味著列名有 10 個字元，使用 12 個字節。這些數字都應該是 9（如果我們計數
id_naslov正確並且所有 9 個字元都是 ASCII），所以那裡發生了一些可疑的事情。您可以
hex(column_name)在最後一個查詢的選擇列表中添加，我們將知道列名的確切含義。然後您可以將其更改為僅具有可列印的 ascii 字元。要修復，你需要這樣的東西：
set @column := X'EFBBBF69645F6E61736C6F76' ; set @qry:= concat('alter table krneki_2 change column ', @column, ' id_naslov smallint') ; prepare stmt from @qry ; execute stmt ;