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 @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',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 = ??? ;