我试图使用一个选择语句从某个MySQL表中获得除一个以外的所有列。有什么简单的方法吗?

编辑:在这个表格中有53列(不是我的设计)


当前回答

我的主要问题是在连接表时获得了许多列。虽然这不是您问题的答案(如何从一个表中选择除某些列之外的所有列),但我认为值得一提的是,您可以指定表。从特定表中获取所有列,而不是仅指定。

下面是一个很有用的例子:

select users.*, phone.meta_value as phone, zipcode.meta_value as zipcode

from users

left join user_meta as phone
on ( (users.user_id = phone.user_id) AND (phone.meta_key = 'phone') )

left join user_meta as zipcode
on ( (users.user_id = zipcode.user_id) AND (zipcode.meta_key = 'zipcode') )

结果是用户表中的所有列,以及从元表中连接的两个附加列。

其他回答

虽然我同意Thomas的答案(+1;)),但我想补充一点,即我假设您不想要的列几乎不包含任何数据。如果它包含大量的文本、xml或二进制blob,那么请花时间单独选择每一列。否则你的表现就会受到影响。干杯!

据我所知,没有。你可以这样做:

SELECT col1, col2, col3, col4 FROM tbl

并手动选择所需的列。然而,如果你想要很多列,那么你可能只需要做一个:

SELECT * FROM tbl 

忽略你不想要的。

针对你的特殊情况,我建议:

SELECT * FROM tbl

除非你只想要几列。如果你只想要四列,那么:

SELECT col3, col6, col45, col 52 FROM tbl

这很好,但如果您想要50个列,那么任何使查询变得(太?)难以阅读的代码。

也许我有一个解决Jan Koritak指出的矛盾的方法

SELECT CONCAT('SELECT ',
( SELECT GROUP_CONCAT(t.col)
FROM
(
    SELECT CASE
    WHEN COLUMN_NAME = 'eid' THEN NULL
    ELSE COLUMN_NAME
    END AS col 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'employee' AND TABLE_SCHEMA = 'test'
) t
WHERE t.col IS NOT NULL) ,
' FROM employee' );

表:

SELECT table_name,column_name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'employee' AND TABLE_SCHEMA = 'test'

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

table_name  column_name
employee    eid
employee    name_eid
employee    sal

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

查询结果:

'SELECT name_eid,sal FROM employee'

您可以使用DESCRIBE my_table并使用其结果动态地生成SELECT语句。

我有一个建议,但不是解决办法。 如果您的一些列有较大的数据集,那么您应该尝试使用以下方法

SELECT *, LEFT(col1, 0) AS col1, LEFT(col2, 0) as col2 FROM table