How to create two auto increment columns using MySQL?-CodePudding

databaseChangeLog:
  - changeSet:
      id: ...
      author: ...
      preConditions:
        - one rror: MARK_RAN
        - not:
            - tableExists:
                tableName: ORDERS
      changes:
        - createTable:
            tableName: ORDERS
            columns:
              - column:
                  name: ID
                  type: INT
                  autoIncrement: true
                  constraints:
                    primaryKey: true
              - column:
                  name: ID_USER
                  type: INT
              ...

        - sql:
            "DELIMITER $$
             CREATE FUNCTION autoInc ()
                 RETURNS INT(10)
                 BEGIN
                     DECLARE getCount INT(10);

                     SET getCount = (
                         SELECT COUNT(USER_ID)
                         FROM ORDERS)   1;

                     RETURN getCount;
                 END$$
             DELIMITER ;"

I am using this approach. When I will insert new entry in orders it should increment user_id too. Because I need user_id as another auto increment column. I am using Liquibase migration to create this table, but I get an error:

Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE FUNCTION autoInc () RETURNS INT(10) BEGIN DECLARE getCount I' at line 1
    ...

Maybe, I do something wrong?

CodePudding user response：

There are a variety of problems with your approach.

First, DELIMITER is a built-in command of the mysql client, it is not recognized by the MySQL Server's SQL parser. Also, I wonder why you are not using https://docs.liquibase.com/change-types/pro/create-function.html

Second, your method of simulating a second auto-increment is flawed. It suffers from a race condition, because if more than one concurrent transaction is inserting a row, they will both get the same result from your SELECT COUNT(*) query, and therefore both attempt to insert the same value for the user_id.

The real auto-increment mechanism in MySQL works outside of transaction scope, so multiple sessions are assured to read the same increment value per table. There is also a brief table-lock while each session increments the table's counter.

To make your function safe from race conditions, you would have to lock the table, to ensure that only one session at a time can read the value.

Third, the auto-increment should be based on MAX(USER_ID), not COUNT(USER_ID) if you're going to calculate the next higher value. If you were to delete a few rows from the table, then COUNT(USER_ID) would generate a value lower than the max value.

Lastly, I can't understand why you would want to make user_id auto-increment at all for an orders table. You would really want every order to generate a new user_id? Aren't user_id's supposed to reference a currently existing user?