选择中的MySQL通配符 [英] MySQL wildcard in select

问题描述

有什么方法可以选择带有通配符的列.

Is there any way to select columns with wild cards.

喜欢

选择名称类型为'SELECT %type% from table_name'的列?

to select columns with names having type could be 'SELECT %type% from table_name' ?

推荐答案

不是.您可以使用 *列通配符来选择所有列.如果要联接多个表，则可以通过在表的前缀或名称前加上*来选择特定表中的所有列:

Not really. You can use the * column wildcard to select all columns. If you're joining multiple tables, you can select all columns from specific table by prefixing * with the table name or alias:

SELECT a.id, a.title, b.*
  FROM articles AS a
    JOIN blurbs AS b ON a.id = b.article

但是，除非正在编写数据库管理程序，否则不应使用*.

However, you shouldn't use * unless you're writing a DB administration program.

或者，您可以通过获取表元数据来获取列名称，从而在SQL或其他语言中构建语句.仅使用MySQL，您可以查询 COLUMNS INFORMATION_SCHEMA 数据库中的表列名称，并使用 GROUP_CONCAT 为语句构建列列表.

Alternatively, you can build a statement within SQL or another language by fetching table metadata to get the column names. Using just MySQL, you can query the COLUMNS table in the INFORMATION_SCHEMA database to get the column names and use GROUP_CONCAT to build the column list for the statement.

SELECT CONCAT(
      'SELECT ',
      GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '),
      ' FROM ', :db, '.', :table,
      ' WHERE ...'
      )
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=:db AND TABLE_NAME=:table

将:db"，:table"和"..."替换为适当的值.您甚至可以将其转换为准备好的语句，以便可以将其用于任何表.从这里开始， PREPARE 和

Replace ":db", ":table" and "..." with the appropriate values. You can even turn it into a prepared statement so you can use it for any table. From there, PREPARE and EXECUTE the constructed statement.

如果您不限于SQL编程，它应该不会太杂乱.您选择的语言的数据库驱动程序可能会提供获取元数据的方法.实际的实现将类似于纯SQL方法(获取列名，汇编语句，准备，执行)，但不应如此丑陋，因为您将使用算法语言而不是声明性语言.

If you're not limited to SQL for programming, it should be less messy. The DB driver for your language of choice likely offers methods to get metadata. The actual implementation would be similar to the pure SQL approach (get column names, assemble statement, prepare, execute), but shouldn't be so ugly, as you'd be using an algorithmic, rather than declarative, language.

我非常有兴趣了解实际需要这样做的情况.

I would be very interested in seeing the situation that this is actually required..

这篇关于选择中的MySQL通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！