在MySql中为视图生成唯一的长ID [英] Generate Unique Long ID for a view in MySql

问题描述

我有三张桌子的观点。它有6列如下：

  ID | NAME | PRINCIPAL_ID |描述| GROUP_ID | TYPE 
  

当我使用 hibernate 来从数据库中检索数据，我不得不在这个视图中放置 ID 列。问题是，我无法生成一个独特的 ID ，这似乎是 hibernate 正确加载数据所必需的。

如何在此视图中为每一行设置唯一的ID？

，我不确定： PRINCIPAL_ID 和 GROUP_ID 的组合可以创建唯一的ID。所有这三个都是 Long 。

解决方案

UPDATE：将它作为一个数字，创建您的视图，如下所示：

  SELECT 
（@rownum：= @ rownum + 1 ）AS rownumber，
 yourTable。* 
 FROM 
 yourTable 
，（SELECT @rownum：= 0）r 
  

但确实如此 - 没有其他可能性了。像我在评论中所说的那样，将rownumber转换为 long ，如果它真的必须是 long 。

或者在程序中：
$ b $ pre $ DELIMITER $$
CREATE PROCEDURE selectFromWhatever（）
BEGIN
SELECT
（@rownum：= @ rownum + 1）AS rownumber，
yourTable。*
FROM
yourTable
，（SELECT @rownum：= 0）r;
END $$
DELIMITER;


然后通过

  CALL selectFromWhatever（）
  

原始答案：

从MySQL手册：

UUID（） b
$ b

返回根据
DCE 1.1：远程过程调用（附录A）由The Open发布的CAE（通用应用程序
环境）规范生成的通用唯一标识符（UUID） 10月份的团体
1997（文件编号C706，
http： //www.opengroup.org/public/pubs/catalog/c706.htm ）。

UUID被设计为一个全球唯一的数字空间和
时间。对UUID（）的两次调用预计会生成两个不同的
值，即使这些调用是在两台不相互连接的单独计算机上执行的。

UUID是一个128位数字，由utf8字符串表示，其格式为aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee格式的五个
十六进制数字：

前三个数字是从时间戳生成的。

第四个数字保留时间唯一性，以防时间戳值丢失单调性（例如，由于日光
节省时间）。

第五个数字是提供空间唯一性的IEEE 802节点号。如果后者不是
可用的（例如，因为主机没有以太网
卡，或者我们不知道如何查找
接口的硬件地址在你的操作系统上）。在这种情况下，空间唯一性
不能得到保证。尽管如此，冲突的概率应该很低
。

目前，只有在FreeBSD和Linux上才考虑接口的MAC地址。在其他操作系统上，MySQL使用
随机生成的48位数字。

mysql> SELECT UUID（）;
- >'6ccd780c-baba-1026-9564-0040f4311e29'

警告

虽然UUID （）值旨在是唯一的，它们不是
必然是不可猜测或不可预测的。如果不可预测性是
所需的，则应以其他方式生成UUID值。注意
$ b

UUID（）不适用于基于语句的复制。

另一种方法是使用 CONCAT（）来建立您的唯一身份证。

 SELECT CONCAT（PRINCIPAL_ID，' - '，GROUP_ID）as myUniqueID 
 FROM yourTable 
  

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.

解决方案

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

这篇关于在MySql中为视图生成唯一的长ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！