如果序列号不存在于数据库中，如何生成序列号

Question

有谁能告诉我最好的方法吗？

1. 我正在尝试生成一个带有唯一代码的序列号

2. 我想检查数据库中是否存在序列号

3. 如果序列号已经存在于数据库中，它会生成一个新的序列号，然后插入。

4. 如果没有序列号，则直接插入数据库

      <div class="col-sm-9">

              <input type="text" name="sn" value="AXJ<?php echo rand(10000, 99999) ?>" class="form-control" id="sn" placeholder="" readonly>

            </div>

我的问题是，检查数据库的功能如何？

Answer 1

我创建了一个示例。从测试数据库和表结构开始：-- Database: `serials`DROP TABLE IF EXISTS `serial`;CREATE TABLE IF NOT EXISTS `serial` (&nbsp; `id` int(10) NOT NULL AUTO_INCREMENT,&nbsp; `serial` varchar(100) NOT NULL,&nbsp; PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;创建一个db.php文件来连接数据库：function connect(){&nbsp; &nbsp; $dsn = 'mysql:host=localhost;port=3306;dbname=serials';&nbsp; &nbsp; try{&nbsp; &nbsp; &nbsp; &nbsp; return new PDO($dsn, 'root', '');&nbsp; &nbsp; }catch( \Exception $e ){&nbsp; &nbsp; &nbsp; &nbsp; echo $e->getMessage();&nbsp; &nbsp; &nbsp; &nbsp; exit;&nbsp; &nbsp; }}如果您还没有准备好插入并且只想向用户显示一个独特的序列号，我创建了form.php并执行了以下操作：<?phprequire_once('./db.php');&nbsp; &nbsp; function createSerial() {&nbsp; &nbsp; &nbsp; &nbsp; $timestamp = time() . rand(10000, 99999);&nbsp; &nbsp; &nbsp; &nbsp; for($i=1;$i<=strlen($timestamp);$i++){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $piece = substr($timestamp, $i - 1, 1);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $shuffle_array[] = $piece;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; shuffle($shuffle_array);&nbsp; &nbsp; &nbsp; &nbsp; return implode('', $shuffle_array);&nbsp; &nbsp; }&nbsp; &nbsp; $pdo = connect();&nbsp; &nbsp; //try up to 5 times&nbsp; &nbsp; for($i=0;$i<=4;$i++) {&nbsp; &nbsp; &nbsp; &nbsp; $serial_number = createSerial();&nbsp; &nbsp; &nbsp; &nbsp; $sql = "SELECT * FROM serial WHERE serial = :serial";&nbsp; &nbsp; &nbsp; &nbsp; $dbh = $pdo->prepare($sql);&nbsp; &nbsp; &nbsp; &nbsp; $dbh->execute( [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ':serial' => $serial_number&nbsp; &nbsp; &nbsp; &nbsp; ]);&nbsp; &nbsp; &nbsp; &nbsp; if( $dbh->rowCount() ) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $serial_number = '';&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }if( $serial_number) { ?><form action="form.php" method="post">&nbsp; &nbsp; <div class="col-sm-9">&nbsp; &nbsp; &nbsp; &nbsp; <input type="text" name="sn" value="AXJ<?php echo $serial_number; ?>" class="form-control" id="sn" placeholder="" readonly>&nbsp; &nbsp; </div><?php}?>还做了一个插入的例子：function createSerial() {&nbsp; &nbsp; $timestamp = time() . rand(10000, 99999);&nbsp; &nbsp; for($i=1;$i<=strlen($timestamp);$i++){&nbsp; &nbsp; &nbsp; &nbsp; $piece = substr($timestamp, $i - 1, 1);&nbsp; &nbsp; &nbsp; &nbsp; $shuffle_array[] = $piece;&nbsp; &nbsp; }&nbsp; &nbsp; shuffle($shuffle_array);&nbsp; &nbsp; return implode('', $shuffle_array);}//database$pdo = connect();//try up to 5 timesfor($i=0;$i<=4;$i++) {&nbsp; &nbsp; $serial_number = createSerial();&nbsp; &nbsp; $sql = "SELECT * FROM serial WHERE serial = :serial";&nbsp; &nbsp; $dbh = $pdo->prepare($sql);&nbsp; &nbsp; $dbh->execute( [&nbsp; &nbsp; &nbsp; &nbsp; ':serial' => $serial_number&nbsp; &nbsp; ]);&nbsp; &nbsp; if( ! $dbh->rowCount() ) {&nbsp; &nbsp; &nbsp; &nbsp; //insert&nbsp; &nbsp; &nbsp; &nbsp; $sql = "INSERT INTO serial VALUES ( :id, :serial)";&nbsp; &nbsp; &nbsp; &nbsp; $dbh2 = $pdo->prepare($sql);&nbsp; &nbsp; &nbsp; &nbsp; $dbh2->execute([&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ':id' => null,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;':serial' => $serial_number&nbsp; &nbsp; &nbsp; &nbsp; ]);&nbsp; &nbsp; &nbsp; &nbsp; break;&nbsp; &nbsp; }}