MySQL插入前检查[关闭]

Question

A more general question about MySQL. I was wondering how much of checks can/should I do directly at SQL level. For example, I'm building a forum where admins can post stickies. Currently, when posting a message, PHP checks the session of the user to see if that user is an admin. My question is: would there be a way to make an INSERT query that only insert the new topic as a sticky (stick = true) if user_is_admin under the user_id of the user currently attempting to post the message is true? Would it be possible/desirable to do that?

Answer 1

To answer part 1 of your question (is it possible?), yes, it is possible.

MySQL has an IF function which can be used within INSERT and UPDATE statements to set the value of column depending on other factors within the scope of the statement.

The syntax is IF(expr,if_true_expr,if_false_expr), so it works a bit like the ternary operator in PHP, C, Java, etc. See http://www.mysqltutorial.org/mysql-if-function.aspx.

As for whether it is desirable, that ulitmately depends on the overall context, but in general using vendor-specific SQL makes your application less portable. Furthermore the logic gets split between the PHP and the SQL so may be less easy to understand and refactor, changes to the DB structure make break the SQL code, you are less likely to realise the benefits of using MVC frameworks which handle the SQL layer for you, etc, so not desirable.