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

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Lead Developer
    @christineh i tried that, initially it was working properly because of having unique PKs combination, but after having huge number of rows getting return from View, i got the duplicate PK again (which was rare, but got that) and again i came back into the same situation where i was earlier. So i tried the solution mention by @Johns but while creating the view it was giving Syntax error but while executing the same in Normal Query was working fine and returning sequence number for each row.
Reply
  • 0
    Certified Lead Developer
    @christineh i tried that, initially it was working properly because of having unique PKs combination, but after having huge number of rows getting return from View, i got the duplicate PK again (which was rare, but got that) and again i came back into the same situation where i was earlier. So i tried the solution mention by @Johns but while creating the view it was giving Syntax error but while executing the same in Normal Query was working fine and returning sequence number for each row.
Children
No Data