A simple and sticky MySQL database implementation

A simple and sticky MySQL database implementation

1. Background: Small series to do a simple Article management page, with the MySQL database. It needs the article top function,

2. The original idea: The wit of the small part of the rapid brain operation, quickly thought to store the article "article" table set an (int) type of "Istop" attribute, 0 means pail top, greater than 0 for the pinned and the larger the number, the higher the priority. The display should only be displayed in descending order of "istop". When the user needs to pin the article "id" to "001", the background only needs to get the maximum value of all the article "Istop" field, and assign it (+1) to "istop" of the article "id" is "001", it can guarantee the highest priority of the article after the top-up operation.

3. Defective implementations: after thinking, write the code immediately. The main or SQL statements are as follows:

Update article Set istop= ((select Max (istop) from article) +1) where id=?;

Using a subquery, the individual feel no problem, but the error

Check it out. MySQL is not supported first select and then update the same table.

4. The final realization: Small series in the online multi-reference and try to find solutions. You can take article data out as a temporary table, and then query Max (istop) in a temporary table, there's no problem. The SQL statements are as follows:

Update article Set istop= ((select Max (istop) from (SELECT * from article) as X) +1) where id=?;

5. Reference link:http://www.jquerycn.cn/a_14968

A simple and sticky MySQL database implementation