MySQL: MySQL查询报错 Error: #1060 列名重复 'id' Error: 1060 Duplicate column name 'id'
1. 问题概述:
为了便于演示,创建表 contacts,并向其中插入一些样本数据:
CREATE TABLE IF NOT EXISTS `contacts` ( `id` int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL, `first_name` varchar(50) NOT NULL, `last_name` varchar(50) NOT NULL, `email` varchar(255) NOT NULL ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `contacts` (`id`, `first_name`, `last_name`, `email`) VALUES (1, 'Carine ', 'Schmitt', '[email protected]'), (2, 'Jean', 'King', '[email protected]'), (3, 'Peter', 'Ferguson', '[email protected]'), (4, 'Janine ', 'Labrune', '[email protected]'), (5, 'Jonas ', 'Bergulfsen', '[email protected]');
自连接查询
SELECT * FROM `contacts` t1 INNER JOIN contacts t2;
id | first_name | last_name | id | first_name | last_name | |||
---|---|---|---|---|---|---|---|---|
1 | Carine | Schmitt | [email protected] | 1 | Carine | Schmitt | [email protected] | |
2 | Jean | King | [email protected] | 1 | Carine | Schmitt | [email protected] | |
... |
并提示:
Error: #1060 列名重复 'id'
2. 相关原因:
当查出来的虚拟表中有相同名称的字段的时候,就需要定义别名。
3. 解决办法:
修改查询语句,例如:
SELECT t1.`id` t1_id, t1.`first_name` t1_first_name, t1.`last_name` t1_last_name, t1.`email` t1_email, t2.`id` t2_id, t2.`first_name` t2_first_name, t2.`last_name` t2_last_name, t2.`email` t2_email FROM `contacts` t1 INNER JOIN contacts t2;
t1_id | t1_first_name | t1_last_name | t1_email | t2_id | t2_first_name | t2_last_name | t2_email | |
---|---|---|---|---|---|---|---|---|
1 | Carine | Schmitt | [email protected] | 1 | Carine | Schmitt | [email protected] | |
2 | Jean | King | [email protected] | 1 | Carine | Schmitt | [email protected] | |
... |