How to reset the initial values of auto-increment columns in a MySQL table

Source: Internet
Author: User

How to reset the initial values of auto-increment columns in a MySQL table

How to reset the initial values of auto-increment columns in a MySQL table

1. Question proposal

In MySQL database design, auto-increment numeric columns are generally designed to serve as the primary key unrelated to the business. After a database is frequently deleted or cleared, its auto-increment value will automatically increase. What should I do if I need to start again?

2. Solution

A. alter table

Delete from table_name; alter table table_name AUTO_INCREMENT = 1;

If there is a lot of data in the database table, the delete operation will last for a long time. Pay attention to this problem.

B. truncate

Truncate table_name

It is simple and quick to clear data directly.

3. delete vs tuncate

The main differences are as follows:

  • Truncate is fast and does not contain logs. Therefore, rollback cannot be performed. Delete.
  • Truncate will reset the index and auto-increment initial values, and delete will not
  • Truncate does not trigger, while delete triggers.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.