MySQL列名称中的通配符 [英] Wildcards in column name for MySQL

问题描述

我正在尝试从数据库中选择多个列，但不是所有列.我要选择的所有列都将以单词"开头.

所以我想用伪代码做到这一点:

SELECT "word%" from searchterms where onstate = 1;

或多或少.我没有找到有关如何执行此操作的任何文档-MySQL中可能吗?基本上，我试图将一行单词与一个标识符一起存储在一个列表中，并且当我提取记录时，我想将所有单词与该标识符相关联.所有这些单词都将作为字符串连接在一起，并通过其标识符传递给数组/字典中的另一个函数.

我正尝试尽可能进行FEW数据库调用，以保持快速的代码.

好，这是你们的另一个问题:

其中将有不定数量的名称为"word"的列.对每一行进行单独的数据库调用会更快，每行都会生成一个Python查询，还是仅使用SELECT *，而仅使用我需要的列会更快?可以说SELECT * NOT XYZ吗?

解决方案

不，SQL不会为您提供任何语法来进行此类选择.

您可以要做的是向MySQL请求

我建议您使用 SQLAlchemy 而不是使用字符串连接，而是为您执行SQL生成. /p>

但是，如果您要做的只是限制列数，则根本不需要执行这样的动态查询.数据库的艰苦工作是选择行.将10列中的5列或全部10列发送给您几乎没有什么区别.

在这种情况下，只需使用"SELECT * FROM ..."并使用Python从结果集中选择列即可.

I am trying to select multiple columns, but not all of the columns, from the database. All of the columns I want to select are going to start with "word".

So in pseudocode I'd like to do this:

SELECT "word%" from searchterms where onstate = 1;

More or less. I am not finding any documentation on how to do this - is it possible in MySQL? Basically, I am trying to store a list of words in a single row, with an identifier, and I want to associate all of the words with that identifier when I pull the records. All of the words are going to be joined as a string and passed to another function in an array/dictionary with their identifier.

I am trying to make as FEW database calls as possible to keep speedy code.

Ok, here's another question for you guys:

There are going to be a variable number of columns with the name "word" in them. Would it be faster to do a separate database call for each row, with a generated Python query per row, or would it be faster to simply SELECT *, and only use the columns I needed? Is it possible to say SELECT * NOT XYZ?

解决方案

No, SQL doesn't provide you with any syntax to do such a select.

What you can do is ask MySQL for a list of column names first, then generate the SQL query from that information.

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'your_table'
    AND column_name LIKE 'word%'

let's you select the column names. Then you can do, in Python:

"SELECT * FROM your_table WHERE " + ' '.join(['%s = 1' % name for name in columns])

Instead of using string concatenation, I would recommend using SQLAlchemy instead to do the SQL generating for you.

However, if all you are doing is limit the number of columns there is no need to do a dynamic query like this at all. The hard work for the database is selecting the rows; it makes little difference to send you 5 columns out of 10, or all 10.

In that case just use a "SELECT * FROM ..." and use Python to pick out the columns from the result set.

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