Alternative column name order in MySQL

Question

When I create a new table that needs user-defined ordering, my first idea always goes to the column name "order". Of course, this is NOT useful as it is a reserved word.

What name do you give this column in your db models?

Thanks for your help.

Answer 1

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

Answer 2

I use "position" instead of "order"

Answer 3

Just add a 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 you to use special characters and keywords, at least it works for the current version of MySql.

Answer 4

SQL Server, at least, allows you to use keywords if they are enclosed in square brackets, although I agree that this is 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 a table, such as UsrSortOrder, so this is not always a problem.

Answer 5

In ANSI / ISO SQL, double quotes delimit keywords when used as column names; String literals are limited to single quotes:

 select "from" = 'from' from foo 

Microsoft SQL Server allows you to use square brackets instead of double quotes:

 select [from] = 'from' from foo 

But either way, this creates a terrible mess for your code (try reading above for someone else.)

If I need a column to organize the results, I usually call it "sequence_number" or "sort_sequence".