4

I have a view over three tables. It has 6 columns as follows:

ID | NAME | PRINCIPAL_ID | DESCRIPTION | GROUP_ID | TYPE

As I'm using hibernate to retrieve data from database, I had to put the ID column in this view. The problem is, I cannot generate a unique ID which seems to be necessary for hibernate to correctly load the data.

How can I have a unique ID for every row in this view?

Maybe this can help, I'm not sure: The combination of PRINCIPAL_ID and GROUP_ID can make a unique ID. All these three are Long.

Matin Kh
  • 5,192
  • 6
  • 53
  • 77

2 Answers2

9

UPDATE: When you insist to have it as a number, create your view like this:

SELECT
(@rownum:=@rownum + 1) AS rownumber,
yourTable.*
FROM
yourTable
, (SELECT @rownum:=0) r

But that's really it - no more other possibilities. Cast rownumber as long like I said in comments, if it really, really has to be long.

Alternatively in a procedure:

DELIMITER $$
CREATE PROCEDURE selectFromWhatever()
BEGIN
SELECT
(@rownum:=@rownum + 1) AS rownumber,
yourTable.*
FROM
yourTable
, (SELECT @rownum:=0) r;
END $$
DELIMITER ;

Then get result with

CALL selectFromWhatever()

Original answer:

From the MySQL manual:

UUID()

Returns a Universal Unique Identifier (UUID) generated according to “DCE 1.1: Remote Procedure Call” (Appendix A) CAE (Common Applications Environment) Specifications published by The Open Group in October 1997 (Document Number C706, http://www.opengroup.org/public/pubs/catalog/c706.htm).

A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.

A UUID is a 128-bit number represented by a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format:

The first three numbers are generated from a timestamp.

The fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (for example, due to daylight saving time).

The fifth number is an IEEE 802 node number that provides spatial uniqueness. A random number is substituted if the latter is not available (for example, because the host computer has no Ethernet card, or we do not know how to find the hardware address of an interface on your operating system). In this case, spatial uniqueness cannot be guaranteed. Nevertheless, a collision should have very low probability.

Currently, the MAC address of an interface is taken into account only on FreeBSD and Linux. On other operating systems, MySQL uses a randomly generated 48-bit number.

mysql> SELECT UUID(); -> '6ccd780c-baba-1026-9564-0040f4311e29'

Warning

Although UUID() values are intended to be unique, they are not necessarily unguessable or unpredictable. If unpredictability is required, UUID values should be generated some other way. Note

UUID() does not work with statement-based replication.

Another way would be to use CONCAT() to build your unique ID.

SELECT CONCAT(PRINCIPAL_ID, '-', GROUP_ID) AS myUniqueID
FROM yourTable
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • can I use it multiple times? Does it generate a `Long` value? – Matin Kh Nov 29 '12 at 10:14
  • Of course you can use it multiple times. No it does not generate a `long` value. – fancyPants Nov 29 '12 at 10:41
  • So, as I understood this is generating unique value each time. The problem is I need a `Long` value for the ID. How is that possible using this `UUID`? – Matin Kh Nov 29 '12 at 11:19
  • 1
    When I use it (for creating the view) it gives me an error. Sounds like I should not use a variable when creating a view. – Matin Kh Dec 01 '12 at 06:04
  • This is giving me this error, when used in creating a view `view's SELECT contains a variable or parameter` – Matin Kh Dec 01 '12 at 08:18
  • 1
    @MatinKh Ah, sorry, forgot about that. Then you have to make it a procedure. Which comes with certain restrictions. Maybe it would be easier if you told me, why it has to be `long`. Why is UUID() not appropriate? There is no way, you can make a unique `long` value out of three `long` columns. The easiest solution would be to just have an auto_increment column in your table. Tell me if you need to know how to accomplish that. – fancyPants Dec 03 '12 at 09:09
  • You're right. Just put a link on `how to write a procedure` and your answer is correct. – Matin Kh Dec 10 '12 at 11:20
1

Alternative is use ROW_NUMBER() OVER ()

SELECT ROW_NUMBER() OVER () AS ROW_NUM ... COLUMN... FROM TABLE
4b0
  • 21,981
  • 30
  • 95
  • 142
Marek Lisiecki
  • 498
  • 6
  • 10