如何在MySQL列中存储查询然后使用该查询?

时间:2020-12-09 15:43:07

For example, say I'm creating a table which stores promo codes for a shipping site. I want to have the table match a promo code with the code's validator, e.g.

例如,假设我正在创建一个存储发货站点促销代码的表格。我想让表格与代码的验证器匹配促销代码,例如

PROMO1: Order must have 3 items
PROMO2: Order subtotal must be greater than $50

I would like to store the query and/or routine in a column in the table, and be able to use the content to validate, in the sense of

我想将查询和/或例程存储在表中的列中,并且能够使用内容来验证,在

SELECT * FROM Orders 
WHERE Promo.ID = 2 AND Promo.Validation = True

Or something to that effect. Any ideas?

或者那种效果。有任何想法吗?

2 个解决方案

#1


1  

I wouldn't save the query in the database, there are far better possibilities.

我不会在数据库中保存查询,有更好的可能性。

You have to decide, which best fits your needs (it's not clear for me based on your question). You can use

你必须决定哪种最适合你的需求(根据你的问题我不清楚)。您可以使用

Views

or

Prepared Statements

or

Stored Procedures

#2


1  

There's probably a better way to solve the issue, but the answer to your question is to write stored procedures that return the results you want. Where I work (and I hate this design), they actually store all queries and dml used by the application in stored procedures.

可能有更好的方法来解决问题,但问题的答案是编写返回所需结果的存储过程。在我工作的地方(我讨厌这个设计),它们实际上存储了应用程序在存储过程中使用的所有查询和dml。

You can also dynamically build your queries using dynamic sql. For MySql, see the post below which might be of some help to you.

您还可以使用动态sql动态构建查询。对于MySql,请参阅下面的帖子,这可能对您有所帮助。

How To have Dynamic SQL in MySQL Stored Procedure

如何在MySQL存储过程中使用动态SQL

Otherwise, you can also store your queries in a string format in the database, and retrieve them and execute them using the EXECUTE statement, such as that post points out.

否则,您还可以在数据库中以字符串格式存储查询,并使用EXECUTE语句检索它们并执行它们,例如该帖子指出。

I'd personally keep away from designs like that though. Storing queries in XML isn't a bad alternative, and have your app be written to be extensible and configurable from XML, so you don't need to make code changes to add new validation logic, and instead just have to configure it in XML.

我个人远离这样的设计。在XML中存储查询并不是一个糟糕的选择,并且可以将您的应用程序编写为可扩展且可从XML进行配置,因此您无需进行代码更改即可添加新的验证逻辑,而只需在XML中进行配置即可。

#1


1  

I wouldn't save the query in the database, there are far better possibilities.

我不会在数据库中保存查询,有更好的可能性。

You have to decide, which best fits your needs (it's not clear for me based on your question). You can use

你必须决定哪种最适合你的需求(根据你的问题我不清楚)。您可以使用

Views

or

Prepared Statements

or

Stored Procedures

#2


1  

There's probably a better way to solve the issue, but the answer to your question is to write stored procedures that return the results you want. Where I work (and I hate this design), they actually store all queries and dml used by the application in stored procedures.

可能有更好的方法来解决问题,但问题的答案是编写返回所需结果的存储过程。在我工作的地方(我讨厌这个设计),它们实际上存储了应用程序在存储过程中使用的所有查询和dml。

You can also dynamically build your queries using dynamic sql. For MySql, see the post below which might be of some help to you.

您还可以使用动态sql动态构建查询。对于MySql,请参阅下面的帖子,这可能对您有所帮助。

How To have Dynamic SQL in MySQL Stored Procedure

如何在MySQL存储过程中使用动态SQL

Otherwise, you can also store your queries in a string format in the database, and retrieve them and execute them using the EXECUTE statement, such as that post points out.

否则,您还可以在数据库中以字符串格式存储查询,并使用EXECUTE语句检索它们并执行它们,例如该帖子指出。

I'd personally keep away from designs like that though. Storing queries in XML isn't a bad alternative, and have your app be written to be extensible and configurable from XML, so you don't need to make code changes to add new validation logic, and instead just have to configure it in XML.

我个人远离这样的设计。在XML中存储查询并不是一个糟糕的选择,并且可以将您的应用程序编写为可扩展且可从XML进行配置,因此您无需进行代码更改即可添加新的验证逻辑,而只需在XML中进行配置即可。