mysql union结果中的错误列 [英] mysql union wrong columns in result
问题描述
使用并集时，列名错误.
I am getting wrong column names when using union.
这是我的工作，我有两个非常大的表，它们具有相同的结构和不同的记录，所以就在这里.
Here is what i do, i have two very big tables with same structure and different records, so here it is.
mysql> select * from e18 where `15` like '%car%' limit 1;
+------+------+----+------+------+------+------+------+------+------+------+------+------+------+------+-------------+------+------+------+------+------+------+--------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-----------+
| id | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 | 37 | 38 |
+------+------+----+------+------+------+------+------+------+------+------+------+------+------+------+-------------+------+------+------+------+------+------+--------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-----------+
| 2730 | 2730 | 18 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | cars: stuff | NULL | NULL | NULL | NULL | NULL | NULL | 5 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | yy |
+------+------+----+------+------+------+------+------+------+------+------+------+------+------+------+-------------+------+------+------+------+------+------+------+--------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-----------+
1 row in set
mysql> (select * from e8 where `15` like '%car%') union
(select * from e10 where `15` like '%car%') union
(select * from e18 where `15` like '%car%') limit 1;");
+------+------+----+------+------+------+------+------+------+------+------+------+------+------+------+------+-------------+------+------+------+------+------+------+------+--------+------+------+------+------+------+------+------+------+------+------+------+------+------+-----------+
| id | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 | 37 | 38 | 15 |
+------+------+----+------+------+------+------+------+------+------+------+------+------+------+------+------+-------------+------+------+------+------+------+------+------+--------+------+------+------+------+------+------+------+------+------+------+------+------+------+-----------+
| 2730 | 2730 | 18 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | cars: stuff | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 5 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | yy |
+------+------+----+------+------+------+------+------+------+------+------+------+------+------+------+------+-------------+------+------+------+------+------+------+------+--------+------+------+------+------+------+------+------+------+------+------+------+------+------+-----------+
1 row in set
在这种情况下，全部工会和工会返回相同的结果.
Union all and union return same result in this case.
在表e18中只有一行包含单词part"car"的行.
There is only one row with word part "car" in it and it is in table e18.
由于某种原因，我从使用usion得到的结果中的列名被弄乱了，好像我缺少了什么，有什么想法吗?
谢谢.
推荐答案
联盟按列 position NOT名称起作用.但是您没有指定列位置，因为您做了*，所以它按数据库选择的顺序排列，但不是您选择的.
Union works by column position NOT name. But you have not specified the column position because you did * so it's in some order picked by the database, but not picked by you.
最终结果集的名称是联合中第一个查询中的列的名称.
The name of the final result set is the name of the columns in the first query in the union.
修复很容易:写出所需的所有列的名称，并确保所有三个查询之间的顺序保持一致.
The fix is easy: Write out the names of all the columns you want, and make sure to keep the order consistent between all three queries.
这些列不是按名称排序的(因此重命名这些列将无济于事)，该顺序是数据库中的一些内部顺序.
The columns are NOT sorted by name (so renaming the columns will not help you), the order is some internal order in the database.
使用*被认为是较差的做法:您不知道所得到的内容，如果只需要某些列，则使用*会检索到更多必要的数据，从而使速度变慢.
Using * is considered poor practice: You don't know what you are getting, and if you only need some of the columns then using * retrieves more data then necessary, making things slower.
BTW这样的命名列(按数字)是非常差的编程习惯.您如何在世界上保持直率?您的列有数字，表有数字.您是否要编写混淆代码?要确保没有其他人可以使用您的代码?因为如果您是的话，这是做到这一点的一种方法.
BTW Naming columns like this (by number) is very poor programming practice. How in the world do you keep things straight? Your columns have numbers, your tables have numbers. Are you trying to write obfuscated code? To make sure no one else can ever work on your code? Because if you are, this is one way to do it.
这篇关于mysql union结果中的错误列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
