将SHOW COLUMNS的输出分组到以逗号分隔的列表中 [英] Group output of SHOW COLUMNS into comma-delimited list

问题描述

我正在将开发服务器上的数据库表与实时服务器进行比较，以查找列名更改，新列和已删除的列.我想做这样的事情:

I am comparing database tables on a development server against a live server, looking for column name changes, new columns, and columns that have been dropped. I'd like to do something like this:

SELECT GROUP_CONCAT(Field) FROM (SHOW COLUMNS ON table_name) GROUP BY Field

我需要的是一个用逗号分隔的列表，然后我可以将其带到实时服务器并执行以下操作:

What I am after is a comma-delimited list that I can then take to the live server and do:

SHOW COLUMNS FROM table_name WHERE NOT IN ([comma-delimited list from above query])

关于如何最好地做到这一点的任何想法-通过以我自己的方式纠正我，还是通过其他方式一起使用?显然，上述SQL无法正常工作.

Any thoughts on how best to do this - either by correcting me in my own approach, or by another means all together? Obviously, the above SQL does not work.

注意:服务器是完全独立的，可能无法相互通信，因此无法进行直接比较.

A note: The servers are entirely separate and may not communicate with each other, so no direct comparison is possible.

编辑

谢谢大家的回答！将您的答案应用于该问题，这是获取列名称的最终SQL:

Thanks for the answers, guys! Applying your answers to the question, this is my final SQL to get the column names:

SELECT CONCAT("'", GROUP_CONCAT(column_name ORDER BY ordinal_position SEPARATOR "', '"), "'") AS columns
FROM information_schema.columns
WHERE table_schema = 'db_name' AND table_name = 'tbl_name'

这给了我一个看起来像这样的列表:

That gives me a list that looks like this:

'id', 'name', 'field1', 'field2'

然后我可以使用此查询进行比较:

Then I can use this query to compare:

SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position)
FROM information_schema.columns
WHERE table_schema = 'db_name' AND table_name = 'tbl_name' AND column_name NOT IN ('id', 'name', 'field1', 'field2')

结果是第一个数据库而不是第二个数据库中存在的所有列的列表.完美！

The results are a list of any columns that exist in the first database and not in the second. Perfect!

推荐答案

看看information_schema.columns表

Take a look at the information_schema.columns table

select group_concat(column_name order by ordinal_position)
from information_schema.columns
where table_schema = 'database_name' and table_name = 'table_name'

编辑.信息模式允许您对元数据进行查询. 因此，您甚至可以比较具有左连接的表之间的字段.

edit. Information schema allows you to make queries on metadata. So, you can even compare fields between tables with a left join for example.

编辑.克里斯，你好很高兴您解决了.正如您所说，您的问题完全不同，因为它涉及不同的服务器.我在同一服务器上添加了两个不同数据库的示例.

edit. Hi Chris. Glad you've solved. As you said your problem was quite different because it concerned with different servers. I add an example of two different databases on the same server.

create database db1;
use db1;
create table table1(
id int not null auto_increment primary key,
name varchar(50),
surname varchar(50),
dob date)
engine = myisam;

create database db2;
create table db2.table2 like db1.table1;
alter table db2.table2 drop column dob;

select i1.column_name from (
select column_name
from information_schema.columns 
where table_schema = 'db1' and table_name = 'table1' ) as i1
left join (
select column_name
from information_schema.columns 
where table_schema = 'db2' and table_name = 'table2' ) as i2
on i1.column_name = i2.column_name
where i2.column_name is null

明显的结果是出现在表1中而不是表2中的dob.

and the obvious result is dob that is present in table1 and not in table2.

希望它对其他人有帮助.问候大家. :)

Hope that it helps someone else. Regards guys. :)

这篇关于将SHOW COLUMNS的输出分组到以逗号分隔的列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！