auto_increment 에 대한 고찰 (Insert, Update)

auto_increment 컬럼에 '0' 값 데이터가 발생하였다. 

원인을 파악하다보니 Insert 에서는 0값이 입력되면 자동증가값 + 1 으로 순차증가하는데 update 하는 경우 0이 그대로 반영됨을 발견!

출처 : https://dev.mysql.com/doc/refman/5.7/en/create-table.html

AUTO_INCREMENT

An integer or floating-point column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexedAUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table.AUTO_INCREMENT sequences begin with 1.

테스트를 해보자

테이블을 생성하고 최초 auto_increment 값을 확인

부연 설명

last_insert_id() 는 insert 실행 후 auto_increment 현재값을 보여준다.

insert DML을 실행하지 않고 알고 싶을땐 아래와 같이 질의 한다. (이때 값은 다음 입력 예정 auto_increment 값)

mysql> select auto_increment from information_schema.tables where table_name = 'tb1';

+----------------+

| auto_increment |

+----------------+

|              6 |

+----------------+

1 row in set (0.01 sec)

 데이터를 넣을때 auto_increment =0 으로 넣어도 순차값으로 입력된다.

이제 0으로 update 해보자.. 성공;

파장이 컸던 이유는

auto_increment 컬럼과 다른 키가  PRIMARY KEY 로 잡혀있어서(복합키) 가능했다; 두둥! 그래서 0으로 데이터들이 대량으로 update;;

CREATE TABLE `child` (

  `child_idx` int(11) NOT NULL AUTO_INCREMENT,

  `parent_idx` int(11) NOT NULL,

  `name` varchar(45) DEFAULT NULL,

  PRIMARY KEY (`child_idx`, `parent_idx`),

  UNIQUE KEY `parent_idx_unique` (`parent_idx`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select * from child;

+-----------+------------+-----------+

| child_idx | parent_idx | name      |

+-----------+------------+-----------+

|         1 |          1 | 개구리    |

|         2 |          2 | 멍멍이    |

+-----------+------------+-----------+

2 rows in set (0.00 sec)

mysql> insert into child (child_idx, parent_idx, name) values (0, 2,'멍멍이')

    -> ON DUPLICATE KEY UPDATE child_idx=0, parent_idx=2, name='찰리';

Query OK, 2 rows affected (0.00 sec)

mysql> select * from child;

+-----------+------------+-----------+

| child_idx | parent_idx | name      |

+-----------+------------+-----------+

|         0 |          2 | 찰리      |

|         1 |          1 | 개구리    |

+-----------+------------+-----------+

2 rows in set (0.00 sec)

 

mysql> insert into child (child_idx, parent_idx, name) values (0, 2,'멍멍이')

    -> ON DUPLICATE KEY UPDATE child_idx=0, parent_idx=1, name='찰리';

ERROR 1062 (23000): Duplicate entry '1' for key 'parent_idx_unique'

mysql> insert into child (child_idx, parent_idx, name) values (0, 1,'개구리')

    -> ON DUPLICATE KEY UPDATE child_idx=0, parent_idx=1, name='찰리';

Query OK, 2 rows affected (0.00 sec)

mysql> select * from child;

+-----------+------------+--------+

| child_idx | parent_idx | name   |

+-----------+------------+--------+

|         0 |          1 | 찰리   |

|         0 |          2 | 찰리   |

+-----------+------------+--------+

2 rows in set (0.00 sec)