mysql – 数据库中扩展前的增量文件名

我有一个脚本,它上传文件并将文件名的详细信息存储在数据库中.当文档上传时,如果DOCUMENT_ID已经存在,我希望能够更新数据库中文件的名称,以增加数字,例如_1,_2,_3(在文件扩展名之前).表结构如下所示：

ID   |  DOCUMENT_ID  |  NAME            |  MODIFIED     |   USER_ID
33   |  81           |  document.docx   |  2014-03-21   |   1
34   |  82           |  doc.docx        |  2014-03-21   |   1
35   |  82           |  doc.docx        |  2014-03-21   |   1
36   |  82           |  doc.docx        |  2014-03-21   |   1

所以在上面的例子中,我希望ID 35 NAME为doc_1.docx,ID 36 NAME为doc_2.docx.

这是我到目前为止所处的地方.我检索了上传的最后一个文件详细信息：

$result1 = mysqli_query($con,"SELECT ID,DOCUMENT_ID,NAME,MODIFIED 
FROM b_bp_history ORDER BY ID DESC LIMIT 1");

while($row = mysqli_fetch_array($result1))
{
$ID = $row['ID'];
$documentID = $row['DOCUMENT_ID'];
$documentName = $row['NAME'];
$documentModified = $row['MODIFIED'];
}

因此,这将为我提供我需要查看DOCUMENT_ID是否已存在的详细信息.现在我认为最好通过执行以下操作来查看它是否存在：

$sql = "SELECT ID,DOCUMENT_ID 
FROM b_bp_history WHERE DOCUMENT_ID = $documentID";
$result2 = mysqli_query($sql);

if(mysqli_num_rows($result2) >0){

/* This is where I need my update */

} else {

/* I don't need an update in here as it will automatically add to the database 
table with no number after it.  Not sure if I should always add the first one 
with a _1 after it so the increment is easy? */

}

从上面我可以看到,我需要在那里进行更新,它基本上会检查名称后面是否存在数字,如果确实存在,则将其递增1.在else语句中,即如果DOCUMENT_ID尚不存在,我可以添加带有_1.docx的第一个,这样增量会更容易吗？

如果DOCUMENT_ID已经存在,则前半部分的更新需要检查扩展前的最后一个数字并增加1,所以如果它是_1那么接下来将是_2.不知道如何做到这一点.我想要的最终结果是：

ID   |  DOCUMENT_ID  |  NAME              |  MODIFIED     |   USER_ID
33   |  81           |  document.docx     |  2014-03-21   |   1
34   |  82           |  doc.docx          |  2014-03-21   |   1
35   |  82           |  doc_1.docx        |  2014-03-21   |   1
36   |  82           |  doc_2.docx        |  2014-03-21   |   1

我希望能解释一下,谢谢你的帮助.

干杯,
安迪 最佳答案 在MySQL中生成序列ID值以表示基于修订ID的命名约定

I used MySQL 5.5.32 to develop and test this solution. Be sure to review the bottom section of my solution for a few homework assignments for future consideration in your overall design approach.

要求和初步评论摘要

外部脚本写入文档历史记录表.有关用户提交的文件的元信息保存在此表中,包括其用户指定的名称. OP请求SQL更新语句或DML操作的过程块,它将原始文档名称重新分配给表示离散REVISION ID概念的名称.

>原始表设计包含一个独立的主键：ID
> DOCUMENT_ID(可能由脚本本身在外部分配的数字ID)与MODIFIED(表示提交/记录文档的最新版本的DATE类型值)之间的关系中也存在隐含的业务键.

Although other RDBMS systems have useful objects and built-in features such as Oracle’s SEQUENCE object and ANALYTICAL FUNCTIONS,There are options available with MySQL’s SQL based capabilities.

设置工作架构

下面是用于构建此解决方案中讨论的环境的DDL脚本.它应该与OP描述匹配一个例外(下面讨论)：

 CREATE TABLE document_history 
(
 id int auto_increment primary key,document_id int,name varchar(100),modified datetime,user_id int 
 );

 INSERT INTO document_history (document_id,name,modified,user_id)
 VALUES
   (81,'document.docx',convert('2014-03-21 05:00:00',datetime),1),(82,'doc.docx',convert('2014-03-21 05:30:00',convert('2014-03-21 05:35:00',convert('2014-03-21 05:50:00',1);

 COMMIT;      

DOCUMENT_HISTORY表是为名为MODIFIED的列设计的DATETIME类型列.否则,document_history表中的条目很可能会为围绕以下复合业务键组合组织的查询返回多个记录：DOCUMENT_ID和MODIFIED.

如何提供顺序修订ID分配

基于SQL的分区行计数的创造性解决方案位于较旧的帖子中：@bobince为ROW_NUMBER() in MySQL.

适用于此任务的SQL查询：

 select t0.document_id,t0.modified,count(*) as revision_id
   from document_history as t0
   join document_history as t1
     on t0.document_id = t1.document_id
    and t0.modified >= t1.modified
  group by t0.document_id,t0.modified
  order by t0.document_id asc,t0.modified asc;

使用提供的测试数据生成此查询的结果：

 | DOCUMENT_ID |                     MODIFIED | REVISION_ID |
 |-------------|------------------------------|-------------|
 |          81 | March,21 2014 05:00:00+0000 |           1 |
 |          82 | March,21 2014 05:30:00+0000 |           1 |
 |          82 | March,21 2014 05:35:00+0000 |           2 |
 |          82 | March,21 2014 05:50:00+0000 |           3 |

请注意,修订标识序列遵循检入每个版本的正确顺序,并且在计算与不同文档标识相关的新系列修订时,修订顺序会正确重置.

EDIT: A good comment from @ThomasKhne is to consider keeping this REVISION_ID as a persistent attribute of your version tracking table. This could be derived from the assigned file name,but it may be preferred because an index optimization to a single-value column is more likely to work. The Revision ID alone may be useful for other purposes such as creating an accurate SORT column for querying a document’s history.

使用MySQL字符串操作函数

修订标识还可以受益于其他约定：列名称宽度的大小应该适应附加的修订版ID后缀.一些MySQL字符串操作将有助于：

 -- Resizing String Values:

 SELECT SUBSTR('EXTRALONGFILENAMEXXX',1,17) FROM DUAL

 | SUBSTR('EXTRALONGFILENAMEXXX',17) |
 |-------------------------------------|
 |                   EXTRALONGFILENAME |     


 -- Substituting and Inserting Text Within Existing String Values:

 SELECT REPLACE('THE QUICK 

 select replace(docrec.name,'.',CONCAT('_',rev.revision_id,'.')) as new_name,rev.document_id,rev.modified
   from (
          select t0.document_id,count(*) as revision_id
            from document_history as t0
            join document_history as t1
              on t0.document_id = t1.document_id
             and t0.modified >= t1.modified
           group by t0.document_id,t0.modified
           order by t0.document_id asc,t0.modified asc
       ) as rev

  join document_history as docrec
    on docrec.document_id = rev.document_id
   and docrec.modified = rev.modified;

 |        NEW_NAME | DOCUMENT_ID |                     MODIFIED |
 |-----------------|-------------|------------------------------|
 | document_1.docx |          81 | March,21 2014 05:00:00+0000 |
 |      doc_1.docx |          82 | March,21 2014 05:30:00+0000 |
 |      doc_2.docx |          82 | March,21 2014 05:35:00+0000 |
 |      doc_3.docx |          82 | March,21 2014 05:50:00+0000 |

 ...
   and docrec.modified = rev.modified
 WHERE docrec.id = ??? ;