<tfoot id='unrjO'></tfoot>
    1. <small id='unrjO'></small><noframes id='unrjO'>

      <i id='unrjO'><tr id='unrjO'><dt id='unrjO'><q id='unrjO'><span id='unrjO'><b id='unrjO'><form id='unrjO'><ins id='unrjO'></ins><ul id='unrjO'></ul><sub id='unrjO'></sub></form><legend id='unrjO'></legend><bdo id='unrjO'><pre id='unrjO'><center id='unrjO'></center></pre></bdo></b><th id='unrjO'></th></span></q></dt></tr></i><div id='unrjO'><tfoot id='unrjO'></tfoot><dl id='unrjO'><fieldset id='unrjO'></fieldset></dl></div>

      <legend id='unrjO'><style id='unrjO'><dir id='unrjO'><q id='unrjO'></q></dir></style></legend>
        • <bdo id='unrjO'></bdo><ul id='unrjO'></ul>

        在 MySQL 中定义具有自动增量的复合键

        Defining Composite Key with Auto Increment in MySQL(在 MySQL 中定义具有自动增量的复合键)
          <tfoot id='Hqh7k'></tfoot>

            <small id='Hqh7k'></small><noframes id='Hqh7k'>

              • <bdo id='Hqh7k'></bdo><ul id='Hqh7k'></ul>

              • <i id='Hqh7k'><tr id='Hqh7k'><dt id='Hqh7k'><q id='Hqh7k'><span id='Hqh7k'><b id='Hqh7k'><form id='Hqh7k'><ins id='Hqh7k'></ins><ul id='Hqh7k'></ul><sub id='Hqh7k'></sub></form><legend id='Hqh7k'></legend><bdo id='Hqh7k'><pre id='Hqh7k'><center id='Hqh7k'></center></pre></bdo></b><th id='Hqh7k'></th></span></q></dt></tr></i><div id='Hqh7k'><tfoot id='Hqh7k'></tfoot><dl id='Hqh7k'><fieldset id='Hqh7k'></fieldset></dl></div>

                    <tbody id='Hqh7k'></tbody>
                  <legend id='Hqh7k'><style id='Hqh7k'><dir id='Hqh7k'><q id='Hqh7k'></q></dir></style></legend>
                  本文介绍了在 MySQL 中定义具有自动增量的复合键的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  场景:

                  我有一个引用两个外键的表,对于这些外键的每个唯一组合,都有自己的 auto_increment 列.我需要实现一个复合键,以帮助使用这三者的组合(一个外键和一个 auto_increment 列,以及另一个具有非唯一值的列)将行标识为唯一的

                  I have a table which references two foreign keys, and for each unique combination of these foreign keys, has its own auto_increment column. I need to implement a Composite Key that will help identify the row as unique using combination of these three (one foreign keys and one auto_increment column, and one other column with non-unique values)

                  表格:

                  CREATE  TABLE `issue_log` (
                  `sr_no` INT NOT NULL AUTO_INCREMENT ,
                    `app_id` INT NOT NULL ,
                    `test_id` INT NOT NULL ,
                    `issue_name` VARCHAR(255) NOT NULL ,
                  primary key (app_id, test_id,sr_no)
                  );
                  

                  当然,我的查询肯定有问题,因此抛出的错误是:

                  Of course, there has to be something wrong with my query, because of which the error thrown is:

                  ERROR 1075:表定义不正确;只能有一辆汽车列,必须定义为键

                  ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key

                  我想要达到的目标:

                  我有一个应用程序表(以 app_id 作为其主键),每个应用程序都有一组要解决的问题,每个应用程序都有多个测试(因此 test_id col)sr_no col 应该为唯一的 app_id 和 test_id 增加.

                  I have an Application Table (with app_id as its primary key), each Application has a set of Issues to be resolved, and each Application has multiple number of tests (so the test_id col) The sr_no col should increment for unique app_id and test_id.

                  即表中的数据应如下所示:

                  i.e. The data in table should look like:

                  数据库引擎是 InnoDB.我想以尽可能简单的方式实现这一点(即,如果可能,请避免触发器/程序 - 建议用于其他问题的类似案例).

                  The database engine is InnoDB. I want to achieve this with as much simplicity as possible (i.e. avoid triggers/procedures if possible - which was suggested for similar cases on other Questions).

                  推荐答案

                  对于 InnoDB 表,您不能让 MySQL 自动为您执行此操作 - 您需要使用触发器或过程,或者使用其他数据库引擎,例如 MyISAM.只能对单个主键进行自动递增.

                  You can't have MySQL do this for you automatically for InnoDB tables - you would need to use a trigger or procedure, or user another DB engine such as MyISAM. Auto incrementing can only be done for a single primary key.

                  类似下面的东西应该可以工作

                  Something like the following should work

                  DELIMITER $$
                  
                  CREATE TRIGGER xxx BEFORE INSERT ON issue_log
                  FOR EACH ROW BEGIN
                      SET NEW.sr_no = (
                         SELECT IFNULL(MAX(sr_no), 0) + 1
                         FROM issue_log
                         WHERE app_id  = NEW.app_id
                           AND test_id = NEW.test_id
                      );
                  END $$
                  
                  DELIMITER ;
                  

                  这篇关于在 MySQL 中定义具有自动增量的复合键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Simulating group_concat MySQL function in Microsoft SQL Server 2005?(在 Microsoft SQL Server 2005 中模拟 group_concat MySQL 函数?)
                  Auto-increment - automatic reset for each year(自动递增 - 每年自动重置)
                  MySQL Auto Increment Custom Values(MySQL 自动递增自定义值)
                  Change the step auto_increment fields increment by(更改步骤 auto_increment 字段增量)
                  How to return the value of AUTO INCREMENT column in SQLite with VB6(如何使用 VB6 在 SQLite 中返回 AUTO INCREMENT 列的值)
                  How to insert new row to database with AUTO_INCREMENT column without specifying column names?(如何在不指定列名的情况下使用 AUTO_INCREMENT 列将新行插入到数据库中?)

                    <small id='QPuff'></small><noframes id='QPuff'>

                      <i id='QPuff'><tr id='QPuff'><dt id='QPuff'><q id='QPuff'><span id='QPuff'><b id='QPuff'><form id='QPuff'><ins id='QPuff'></ins><ul id='QPuff'></ul><sub id='QPuff'></sub></form><legend id='QPuff'></legend><bdo id='QPuff'><pre id='QPuff'><center id='QPuff'></center></pre></bdo></b><th id='QPuff'></th></span></q></dt></tr></i><div id='QPuff'><tfoot id='QPuff'></tfoot><dl id='QPuff'><fieldset id='QPuff'></fieldset></dl></div>
                      • <legend id='QPuff'><style id='QPuff'><dir id='QPuff'><q id='QPuff'></q></dir></style></legend>

                            <tbody id='QPuff'></tbody>
                            <bdo id='QPuff'></bdo><ul id='QPuff'></ul>
                            <tfoot id='QPuff'></tfoot>