mysql - 将数据库中的扩展名前的文件名加 1

Question

我有一个脚本可以上传文件并将文件名的详细信息存储在数据库中。当文档被上传时，如果 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? */

}

正如您从上面看到的，我需要在那里进行更新，主要检查名称后面是否存在数字，如果存在，则将其加一。在 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

我希望能解释一下，谢谢你的帮助。

干杯，
安迪

Answer 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 描述相匹配，但有一个异常(exception)(下面讨论):

 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',datetime),1),
   (82, 'doc.docx', convert('2014-03-21 05:35:00',datetime),1),
   (82, 'doc.docx', convert('2014-03-21 05:50:00',datetime),1);

 COMMIT;      

表 DOCUMENT_HISTORY是用 DATETIME 设计的名为 MODIFIED 的列的类型列.否则，document_history 表中的条目很可能会为围绕以下复合业务键组合组织的查询返回多条记录: DOCUMENT_ID和 MODIFIED .
如何提供已排序的修订 ID 分配
基于 SQL 的分区行计数的创造性解决方案在较早的帖子中: ROW_NUMBER() in MySQL来自@bobince。
适合此任务的 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 |

请注意，修订 id 序列遵循每个版本 checkin 的正确顺序，并且在计算与不同文档 id 相关的新修订系列时，修订序列会正确重置。

EDIT: A good comment from @ThomasKöhne 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',1,17) |
 |-------------------------------------|
 |                   EXTRALONGFILENAME |     


 -- Substituting and Inserting Text Within Existing String Values:

 SELECT REPLACE('THE QUICK <LEAN> FOX','<LEAN>','BROWN') FROM DUAL

 | REPLACE('THE QUICK <LEAN> FOX','<LEAN>','BROWN') |
 |--------------------------------------------------|
 |                              THE QUICK BROWN FOX |


 -- Combining Strings Using Concatenation

 SELECT CONCAT(id, '-', document_id, '-', name) 
   FROM document_history

 | CONCAT(ID, '-', DOCUMENT_ID, '-', NAME) |
 |-----------------------------------------|
 |                      1-81-document.docx |
 |                           2-82-doc.docx | 
 |                           3-82-doc.docx |
 |                           4-82-doc.docx |

将它们放在一起:使用修订表示法构建新文件名
使用上一个查询作为基础、内联 View (或子查询)，这是为给定修订日志记录生成新文件名的下一步:
具有修改文件名的 SQL 查询

 select replace(docrec.name, '.', CONCAT('_', rev.revision_id, '.')) as new_name, 
    rev.document_id, rev.modified
   from (
          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
       ) 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 |

这些 ( NEW_NAME ) 值是更新 DOCUMENT_HISTORY 所需的值。 table 。 MODIFIED的检查列 DOCUMENT_ID = 82 表示 checkin 修订按照复合业务键的这一部分的正确顺序编号。
查找未处理的文档记录
如果文件名格式相当一致，则 SQL LIKE运算符可能足以识别已更改的记录名称。 MySQL 还通过 REGULAR EXPRESSIONS 提供过滤功能，这为解析文档名称值提供了更大的灵活性。
剩下的就是弄清楚如何只更新单个记录或一组记录。放置过滤条件的合适位置应该是在别名表之间的连接之后查询的最外层部分:

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

还有其他地方可以优化以加快响应时间，例如在派生修订 ID 值的内部子查询中……您对感兴趣的特定记录集了解得越多，您就可以分割开头SQL 语句只查看感兴趣的内容。
作业:对解决方案的一些总结性评论
这些东西纯粹是可选的，它们代表了在编写本文时在设计和可用性方面想到的一些附带想法。
两步还是一步？
在当前设计中，每条记录有两个离散操作: INSERT通过脚本然后 UPDATE通过 SQL DML 调用获取值。必须记住两个 SQL 命令可能很烦人。考虑为仅插入操作构建第二个表。

使用第二个表 ( DOCUMENT_LIST ) 保存几乎相同的信息，除了可能有两列:

BASE_FILE_NAME (即 doc.docx 或 document.docx)可能适用于多个 HISTORY_ID 值。

FILE_NAME (即 doc_1.docx、doc_2.docx 等)对于每条记录都是唯一的。

设置数据库 TRIGGER在源表上:DOCUMENT_HISTORY并将我们开发的 SQL 查询放入其中。这将在脚本填充历史记录表后的大致同一时刻自动填充正确的修订文件名。

WHY BOTHER? This suggestion mainly fits under the category of SCALABILITY of your database design. The assignment of a revision name is still a two step process, but the second step is now handled automatically within the database, whereas you'd have to remember to include it everywhere you invoked a DML operation on top of the history table.

管理别名
我没有在任何地方看到它，但我认为 USER最初为正在跟踪的文件分配一些名称。最后，这似乎无关紧要，因为它是系统的最终用户永远不会看到的内部跟踪的东西。

For your information, this information isn't portrayed to the customer, it is saved in a table in the database as a version history...

如果“基本”名称在给出后保持不变，则读取给定文档的历史会更容易:

在上面的数据样本中，除非 DOCUMENT_ID已知，可能不清楚列出的所有文件名都是相关的。这可能不一定是一个问题，但从语义的角度来看，将用户分配的文件名分隔为 ALIASES 是一种很好的做法。可以随时更改和分配。
考虑设置一个单独的表来跟踪最终用户给出的“用户友好”名称，并将其与它应该表示的文档 ID 相关联。用户可能会发出数百或数千个重命名请求……而后端文件系统使用更简单、更一致的命名方法。