Generate column with unique ID in MySQL view

I've often found the need to create views which do not have a single-column primary key, but we must have this primary key column in order to publish a data store which connects to such a view. I suspect that other practitioners have encountered the same issue, so I thought I'd write a quick note about the solutions that I have found. Perhaps some of this information could be included in a KB article.

In SQL Server and Oracle, the ROW_NUMBER() function meets our needs. Your view definition might look something like this:

SELECT ISNULL(CAST(ROW_NUMBER() OVER (ORDER BY your_ordering_column_name) AS int), 0) AS id, [other column names]
FROM your_tables

However, in MySQL we have to be more creative. There is a way to generate row numbers by making use of a variable inside the query:

SELECT t.*, @rownum := @rownum + 1 AS rank
FROM YOUR_TABLE t, (SELECT @rownum := 0) r

However, if you try to cre...

OriginalPostID-272952