如何避免在MySQL中重复的随机ID选择？

debugcn 发表于 Dev

主义

我需要id从表中选择两个唯一的随机数nodes，并将其插入表的parent列中edges。但是问题在于它们有时会重复，并且需要它们始终保持不同，请问如何避免这种情况？

编码：

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'DAGtest3'
});

connection.connect((err) => {
  if (err) throw err;
  console.log('Database Connected!');
});

var a = "INSERT INTO `edges` (`parent`, `child`) VALUES ((SELECT `id` FROM `nodes` ORDER BY rand() LIMIT 1 ) , (4) )";

connection.query(a, (err, res) => {
    if(err) throw err;
    console.log("1 edge inserted to previous data");
});
            
var b = "INSERT INTO `edges` (`parent`, `child`) VALUES ((SELECT `id` FROM `nodes` ORDER BY rand() LIMIT 1) , (4) )";
connection.query(b, (err, res) => {
    if(err) throw err;
    console.log("Another edge inserted to previous data");   
});

表的代码：

CREATE TABLE nodes (
    id INTEGER NOT NULL AUTO_INCREMENT,
    sensorvalue VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);
 
CREATE TABLE edges (
    parent INTEGER NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE,
    child INTEGER NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (parent, child)
);
 
CREATE INDEX parent_idx ON edges (parent);
CREATE INDEX child_idx ON edges (child);

提前致谢。

马特·摩根（Matt Morgan）

您不希望有两个连续的随机选择，因为第二个随机选择返回与第一个相同的行的可能性不为零。我认为您应该一次获取两行：

INSERT INTO `edges` (`parent`, `child`) SELECT `id`, 4 FROM `nodes` ORDER BY rand() LIMIT 2;

因此，您的代码将只执行一个查询，而不是两个：

var a = "INSERT INTO `edges` (`parent`, `child`) SELECT `id`, 4 FROM `nodes` ORDER BY rand() LIMIT 2";

connection.query(a, (err, res) => {
    if(err) throw err;
    console.log("2 edges inserted to previous data");
});

当我在本地设置数据库并运行上面的代码时，插入成功：

Database Connected!
2 edges inserted to previous data

当我在控制台中查询数据库时，我得到：

mysql> select * from edges;
+--------+-------+
| parent | child |
+--------+-------+
|      2 |     4 |
|      3 |     4 |
+--------+-------+
2 rows in set (0.00 sec)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。