如何将SQL查询保存到表中？

I am saving an SQL query in a database table, so saving the criteria to be used no matter if more records are inputted into the db.

我正在数据库表中保存SQL查询，因此无论是否将更多记录输入到数据库中，都要保存要使用的条件。

Is there a correct datatype and syntax to use to store the Query statement. I have set the datatype as VARCHAR(1055) as I think that will be enough. Is there a MySQL function that will make sure the text is saved correctly in terms of, quotations and keeping it a single string.

是否有用于存储Query语句的正确数据类型和语法。我已将数据类型设置为VARCHAR（1055），因为我认为这已经足够了。是否有一个MySQL函数，可以确保文本正确保存，引用并保持单个字符串。

Update: Reason for saving query

更新：保存查询的原因

We allow the users of the system to create a list of contact details based on other users of the system, so they create the query using a form to select say all users with job type of executive.

我们允许系统用户基于系统的其他用户创建联系人详细信息列表，因此他们使用表单创建查询，以选择具有执行类型的所有用户。

The above query is then saved in the database, so that even if a new user is added in the executive job type, his contact details will be included when sending communications.

然后将上述查询保存在数据库中，这样即使在执行作业类型中添加了新用户，在发送通信时也会包含其联系人详细信息。

I think this is the best way to do it...do you have any ideas?

我认为这是最好的方式......你有什么想法吗？

4 个解决方案

#1

VARCHAR(1055) will never be enough. Just use TEXT, MySQL's data type for arbitrary-length text (also called CLOB in other databases).

VARCHAR（1055）永远不够。只需使用TEXT，MySQL的数据类型用于任意长度的文本（在其他数据库中也称为CLOB）。

More background info:

更多背景信息：

Which DATATYPE is better to use TEXT or VARCHAR?
哪个DATATYPE最好使用TEXT或VARCHAR？

Nonetheless, I think you should probably model your query in one way or another on the application layer, instead of storing plain text SQL in your database. When you change your schema, all of those SQL statements might be wrong. Good luck migrating, then!

尽管如此，我认为您应该在应用程序层上以某种方式对查询进行建模，而不是在数据库中存储纯文本SQL。更改架构时，所有这些SQL语句可能都是错误的。祝你好运迁移吧！

Another drawback of your approach is that you're creating a big security issue, if users are allowed to enter arbitrary SQL. A nifty intern whose contract wasn't prolonged might store

如果允许用户输入任意SQL，那么您的方法的另一个缺点是您正在创建一个很大的安全问题。一个合同没有延长的漂亮实习生可能存储

DROP DATABASE my_database.

#2

There is no correct data type to store a query.

没有正确的数据类型来存储查询。

But you can always strip HTML chars by using HTMLencode chars.

但您可以使用HTMLencode字符始终删除HTML字符。

Or you can use the PHP htmlentities() to convert the characters

或者您可以使用PHP htmlentities（）来转换字符

#3

If you don't need to pass in parameters you could execute a CREATE VIEW. You could just execute the code from PHP using MySQLi.

如果您不需要传入参数，则可以执行CREATE VIEW。您可以使用MySQLi从PHP执行代码。

CREATE VIEW myquery AS
SELECT * FROM mytable

Usage:

用法：

SELECT * FROM myquery

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

#4

I believe this is not the right approach. You should not store the resulting query in your database, but instead you should store the parameters that resulted in that created sql. In your implementation when you want to change that query, you'd have to parse your stored SQL to get parameters in order to display your filter form to user. However if you keep your parameters instead, you always can regenerate the query when needed, and easily can display current filter form.

我认为这不是正确的做法。您不应将结果查询存储在数据库中，而应存储导致创建的sql的参数。在您希望更改该查询的实现中，您必须解析存储的SQL以获取参数，以便向用户显示过滤器表单。但是，如果您保留参数，则始终可以在需要时重新生成查询，并且可以轻松显示当前过滤器表单。

If you do insist of storing your SQL tho, as noted before me, TEXT is the correct field type, as it is much less likely to cut off your sql string at field size limit.

如果你坚持存储你的SQL，如前所述，TEXT是正确的字段类型，因为它不太可能在字段大小限制下切断你的sql字符串。

#1