MySQL – insert & update

1. ON DUPLICATE UPDATE KEY

Here just record how to use insert & update a row into mysql by one statement in autual development. In fact, there are many ways can acheive the function that inserting a row into database if not exist or upating it. E.g. 1) REPLACE; 2)ON DUPLICATE UPDATE KEY.

The meaning of ON DUPLICATE UPDATE KEY is that if a record has already exited in mysql with unique index, then the latest more inserting will just update other related fields of the existent record, no inserting action happen, otherwise will insert it.

2. Unique index

Unique index guarantees the fields of a column are unique. Of course, you can create a unique idex works for two or more columns when create a table, or alter a existed table. See the SQL script:

CREATE TABLE `statistic_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'auto increment primary key|abei|2019-07-24',
  `statistic_month` varchar(10) NOT NULL DEFAULT '' COMMENT 'statistic month|abei|2019-07-24',
  `customer_id` varchar(10) NOT NULL DEFAULT '' COMMENT 'customer id|abei|2019-07-24',
  `order_total` int(11) NOT NULL DEFAULT '0' COMMENT 'order total|abei|2019-07-24',
  `paid_amount` int(11) NOT NULL DEFAULT '0' COMMENT 'paid amount|abei|2019-07-24',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time|abei|2019-07-24',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time|abei|2019-07-24',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_statistic_month_customer` (`statistic_month`,`customer_id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='pay service - order statistic|abei|2019-07-24';

UNIQUE KEY idx_statistic_month_customer (statistic_month,customer_id), means as long as any one of the two fields( statistic_month & customer_id ) is different of two records, then the two records are considered to be different.

3. Insert & update in one statement

Here, just directly post a example from my actual development, put the statistic result comes from stream computing into database.

<insert id="insertOrUpdateStatisticOrderData" parameterType="com.qinf.study.base.web.entity.StatisticOrderEntity">
    INSERT INTO statistic_order
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="statisticOrderEntity.statisticMonth != null">statistic_month,</if>
        <if test="statisticOrderEntity.customerId != null">customer_id,</if>
        <if test="statisticOrderEntity.orderTotal != null">order_total,</if>
        <if test="statisticOrderEntity.paidAmount != null">paid_amount</if>
    </trim>
    VALUES
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="statisticOrderEntity.statisticMonth != null">
            #{statisticOrderEntity.statisticMonth, jdbcType=VARCHAR},
        </if>
        <if test="statisticOrderEntity.customerId != null">
            #{statisticOrderEntity.customerId, jdbcType=VARCHAR},
        </if>
        <if test="statisticOrderEntity.orderTotal != null">
            #{statisticOrderEntity.orderTotal, jdbcType=INTEGER},
        </if>
        <if test="statisticOrderEntity.paidAmount != null">
            #{statisticOrderEntity.paidAmount, jdbcType=INTEGER}
        </if>
    </trim>
    ON DUPLICATE KEY UPDATE
    <trim suffixOverrides=",">
        <if test="statisticOrderEntity.orderTotal != null">
            order_total = VALUES(order_total),
        </if>
        <if test="statisticOrderEntity.paidAmount != null">
            paid_amount = VALUES(paid_amount)
        </if>
    </trim>
</insert>

Above statement used in MyBatis, one more word, the usage of <trim> of MyBatis. From internet, I just can find the example of <trim> used in “where” or “set”, here I give another example, hope can help us understand it deeply.

Let me try analize above SQL script, we should pay more attention on fields of “statistic_month” and “customer_id”, from business perspective, we statistic oder data of customer by month. If customerId or month is different, then can consider the statistic data is different from others.