Manually automatically increment a column in MySQL

auto-increment.mysql

/**
 * Manually automatically increment a column in MySQL
 * 
 * Ok, so a little introduction is warranted here, because I don't just mean setting
 * a column as 'AUTO_INCREMENT'.
 *
 * I am currently working on a project where a user can sort the records however
 * they like. The order is determined by a column I have named 'sort'. When they
 * input the data, it was random, but they wanted it to sort alphabetically by
 * default, and rearrange from there. This quick script sets the 'sort' column to an
 * incrementing value based on the order clause.
 *
 * There are probably many other uses for this snippet, but this is what I used it
 * for. You could edit the query too, to perform the update with different rules.
 */

# Set the SQL statement variables
SET @table = 'mytable';
SET @column = 'sort';     # The column you want to increment
SET @orderby = 'title';   # The column you want the increment to order by

# Set our incrementing variable to 0
SET @i = 0;

# Prepare the statement
SET @sql = CONCAT('UPDATE ', @table, ' SET ', @column, ' = @i:=@i+1 ORDER BY ', @orderby);
PREPARE q FROM @sql;

# Run the statement
EXECUTE q;