/*
 * Application Note Number:
 *      AN20201231
 * Database:
 *      MySQL, MariaDB
 * Version(s):
 *      5.7/8.0 (MySQL), 10.3 (MariaDB)
 * Purpose:
 *      To demonstrate the following words mentioned in https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
 *          ...
 *          The effects are not quite identical: For an InnoDB table where a is an auto-increment column, the INSERT
 *          statement increases the auto-increment value but the UPDATE does not.
 *          ...
 *
 * Notes:
 *      1. We should know that the values of an auto-increment column is not always consecutive.
 *      2. We should try our best to avoid INSERT ... ON DUPLICATE KEY UPDATE statement.
 *      3. The gaps in the auto-increment columns are normal and should be ignored.
 */
USE playground;

DROP TABLE IF EXISTS t1;

/*
 *  Table t1 is a very common design in modern database applications. Field f_id is used as primary key in the physical
 *  model and to be referred by foreign keys found in other tables. Fields f_ak1, f_ak2, ... are alternative keys used
 *  as unique identifiers in the conceptual model.
 */
CREATE TABLE t1
(
    f_id    int NOT NULL AUTO_INCREMENT,
    f_ak1   int NOT NULL,
    f_ak2   int NOT NULL,
    f_value int NOT NULL,
    PRIMARY KEY (f_id),
    UNIQUE INDEX idx_f_unique (f_ak1, f_ak2)
) ENGINE = InnoDB;

INSERT INTO t1 (f_ak1, f_ak2, f_value)
VALUES (1, 1, 1),
       (2, 2, 2);

SELECT f_id, f_ak1, f_ak2, f_value FROM t1;
/*
** Result Grid:
**
**      |f_id       |f_ak1      |f_ak2      |f_value    |
**      |-----------|-----------|-----------|-----------|
**      |1          |1          |1          |1          |
**      |2          |2          |2          |2          |
*/

/*
** Create a gap, 3
*/
INSERT INTO t1 (f_ak1, f_ak2, f_value)
VALUES (1, 1, 1)
    ON DUPLICATE KEY UPDATE f_ak1   = VALUES(f_ak1),
                            f_ak2   = VALUES(f_ak2),
                            f_value = VALUES(f_value) * 10 + VALUES(f_value);

INSERT INTO t1 (f_ak1, f_ak2, f_value) VALUES (3, 3, 3);

/*
** Create another gap, 5
 */
INSERT INTO t1 (f_ak1, f_ak2, f_value)
VALUES (3, 3, 3)
    ON DUPLICATE KEY UPDATE f_ak1   = VALUES(f_ak1),
                            f_ak2   = VALUES(f_ak2),
                            f_value = VALUES(f_value) * 10 + VALUES(f_value);

INSERT INTO t1 (f_ak1, f_ak2, f_value) VALUES (4, 4, 4);

SELECT f_id, f_ak1, f_ak2, f_value FROM t1;
/*
** Result Grid:
**
**      |f_id       |f_ak1      |f_ak2      |f_value    |
**      |-----------|-----------|-----------|-----------|
**      |1          |1          |1          |11         |
**      |2          |2          |2          |2          |
**      |4          |3          |3          |33         |
**      |6          |4          |4          |4          |
*/

DROP TABLE IF EXISTS t1;