MySQL 시퀀스(AUTO_INCREMENT) 조회 및 초기화

int(11) auto increment 최대 4294967295 이후에는 error

unsigned BIGINT 최대 18446744073709551615 까지 사용 가능 

-- 테이블의 상태 확인

show table status where name='T_HADOOP_LATEST_METRICS';

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

| Name                    | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |

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

| T_HADOOP_LATEST_METRICS | InnoDB |      10 | Compact    | 18124 |            144 |     2621440 |               0 |      7913472 |   4194304 |     4294967295 | 2017-11-06 13:57:16 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |

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

1 row in set (0.00 sec)

-- 시퀀스 조회 (20억 이상)

SELECT table_name,table_comment,table_schema,table_type,engine,

version,row_format,table_rows,avg_row_length,data_length,

max_data_length,index_length,AUTO_INCREMENT,create_time,table_collation,

CEILING((data_length+index_length)/1024/1024) AS total_mb,

CEILING((data_length)/1024/1024) AS data_mb,

CEILING((index_length)/1024/1024) AS index_mb

FROM information_schema.tables 

WHERE table_schema='cloumon'

and Auto_increment > 2000000000

ORDER BY Auto_increment DESC

;

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

| table_name              | table_comment | table_schema | table_type | engine | version | row_format | table_rows | avg_row_length | data_length | max_data_length | index_length | AUTO_INCREMENT | create_time         | table_collation | total_mb | data_mb | index_mb |

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

| T_HADOOP_LATEST_METRICS |               | cloumon      | BASE TABLE | InnoDB |      10 | Compact    |      18628 |            140 |     2621440 |               0 |      7913472 |     4294967295 | 2017-11-06 13:57:16 | utf8_general_ci |       11 |       3 |        8 |

| T_HOST_LATEST_METRICS   |               | cloumon      | BASE TABLE | InnoDB |      10 | Compact    |      17253 |             92 |     1589248 |               0 |      3178496 |     4294967295 | 2017-11-06 13:57:18 | utf8_general_ci |        5 |       2 |        4 |

| T_HBASE_LATEST_METRICS  |               | cloumon      | BASE TABLE | InnoDB |      10 | Compact    |      14489 |            182 |     2637824 |               0 |      8454144 |     4142014594 | 2017-11-06 13:57:17 | utf8_general_ci |       11 |       3 |        9 |

| T_MR_LATEST_METRICS     |               | cloumon      | BASE TABLE | InnoDB |      10 | Compact    |       3402 |            105 |      360448 |               0 |      1081344 |     2501997189 | 2017-11-06 13:57:20 | utf8_general_ci |        2 |       1 |        2 |

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

4 rows in set (2.59 sec)

-- 시퀀스 초기화

use cloumon;

alter table T_HADOOP_LATEST_METRICS auto_increment=1;

alter table T_HOST_LATEST_METRICS auto_increment=1;

alter table T_HBASE_LATEST_METRICS auto_increment=1;

alter table T_MR_LATEST_METRICS auto_increment=1;