Alternative to column name "order" in MySQL

When I create a new table that need an ordering defined by the user, my first idea always go to a column name "order". Of course, this is NOT good since it's a reserved word.

Which name are you giving to that column in your database models ?

I use "position" in place of "order"

I often use simple synonyms, "sort" for example.

Just add the tick mark ` around the names of your tables and columns, for example:

  CREATE TABLE `order`
  (
         `order#` char(4) NOT NULL,
         `ord_date` DATE,
         Primary Key (`order#`)
  )
  ENGINE=InnoDB;

This allows for special characters and keywords to be used, at least this works for the current version of MySql.

In ANSI/ISO SQL, double quotes delimit keywords when used as column names; string literals are delimited by single quotes:

select "from" = 'from' from foo

Microsoft SQL Server allows the use of square brackets in lieu of the double quotes as well:

select [from] = 'from' from foo

But either way, it makes a dreadful mess of your code (try reading the above to someone.)

If I need an column for ordering results, I generally call it something like 'sequence_number' or 'sort_sequence'.

SQL Server, at least, allows you to use keywords if enclosed in square brackets, although I agree it's not a great idea.

I believe the last time I did this, I used SortOrder for the name. However, I often use prefixes that reflect the table such as UsrSortOrder so that's not always an issue.