How to reset AUTO_INCREMENT in MySQL

You can reset the counter with:

ALTER TABLE tablename AUTO_INCREMENT = 1

For InnoDB you cannot set the auto_increment value lower or equal to the highest current index. (quote from ViralPatel):

Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

See How can I reset an MySQL AutoIncrement using a MAX value from another table? on how to dynamically get an acceptable value.

Related Posts:

  1. How to create id with AUTO_INCREMENT on Oracle?
  2. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  3. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  4. SQL WITH clause example [duplicate]
  5. The wait operation timed out. ASP
  6. Conversion failed when converting date and/or time from character string while inserting datetime
  7. how to fix oracle ORA-01722 invalid number error
  8. SQL query to select dates between two dates
  9. MySQL – Operand should contain 1 column(s)
  10. SQL SELECT WHERE field contains words
  11. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  12. NOT IN vs NOT EXISTS
  13. Must declare the scalar variable
  14. ORA-00979 not a group by expression
  15. MySQL query String contains
  16. How do I escape a single quote in SQL Server?
  17. T-SQL split string based on delimiter
  18. Finding duplicate values in a SQL table
  19. MySQL Cannot Add Foreign Key Constraint
  20. Oracle error : ORA-00905: Missing keyword
  21. How do I import an SQL file using the command line in MySQL?
  22. Sql Server equivalent of a COUNTIF aggregate function
  23. Column name or number of supplied values does not match table definition
  24. How do I do multiple CASE WHEN conditions using SQL Server 2008?
  25. Simple PHP Pagination script
  26. Difference between JOIN and INNER JOIN
  27. ORA-12560: TNS:protocol adaptor error
  28. What is the difference between JOIN and UNION?
  29. MySQL Multiple Joins in one query?
  30. “CASE” statement within “WHERE” clause in SQL Server 2008
  31. ORA-00972 identifier is too long alias column name
  32. What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?
  33. SQL – HAVING vs. WHERE
  34. Combining “LIKE” and “IN” for SQL Server
  35. Oracle – ORA-01489: result of string concatenation is too long [duplicate]
  36. SQL Server WITH statement
  37. How to SUM two fields within an SQL query
  38. DateTime2 vs DateTime in SQL Server
  39. Difference between numeric, float and decimal in SQL Server
  40. “ORA-01438: value larger than specified precision allowed for this column” when inserting 3
  41. Algebra Relational sql GROUP BY SORT BY ORDER BY
  42. Oracle “Partition By” Keyword
  43. ERROR: there is no unique constraint matching given keys for referenced table “bar”
  44. What is the equivalent of ‘describe table’ in SQL Server?
  45. How do I query for all dates greater than a certain date in SQL Server?
  46. Add a column with a default value to an existing table in SQL Server
  47. Cast from VARCHAR to INT – MySQL
  48. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  49. Using group by on multiple columns
  50. What is the purpose of using WHERE 1=1 in SQL statements?
  51. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  52. How do you force mysql LIKE to be case sensitive?
  53. ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  54. What is the difference between a stored procedure and a view?
  55. Bulk load data conversion error (truncation)
  56. Insert into a MySQL table or update if exists
  57. Convert timestamp to date in Oracle SQL
  58. How to order by with union in SQL?
  59. Can I use multiple “with”?
  60. PostgreSQL create table if not exists
  61. Incorrect syntax near ”
  62. MySQL: Can’t create table (errno: 150)
  63. Join vs. sub-query
  64. Alternative to except in MySQL
  65. DATEDIFF function in Oracle
  66. GROUP BY and COUNT using ActiveRecord
  67. MySQL – Get row number on select
  68. Arithmetic overflow error converting varchar to data type numeric. ’10’ <= 9.00
  69. Difference between EXISTS and IN in SQL?
  70. What does * mean in sql?
  71. How to truncate the text returned for a column in a MySQL query
  72. INSERT VALUES WHERE NOT EXISTS
  73. Using union and order by clause in mysql
  74. Difference between `load data inpath ` and `location` in hive?
  75. How to implement LIMIT with SQL Server? [duplicate]
  76. What’s the best way to dedupe a table?
  77. Moving database with phpMyAdmin
  78. How To Write An Inner Join With WP Query
  79. How do I escape a table name or column name in SQL? esc_sql doesn’t do this
  80. Update user_login, user_nicename, and display_name
  81. Change sticky status of posts from phpMyAdmin
  82. Fetch all Posts where logged in user has commented
  83. Best Way to Merge a Dev and Live Site to Become a Staging Site?
  84. Needing to move content from postmeta to posts in sql [closed]
  85. How to import a Typo3 database to a wordpress site?
  86. What does the $posts_join filter join to?
  87. Clean up very big and very dirty database
  88. How can I convert everything from category X to have post format Link
  89. Bulk delete WordPress Post and all metadata, etc using SQL query
  90. What steps do I need to take to install a local copy of a live website?
  91. Reset post IDs with all post meta
  92. How to Add or Change Post Title
  93. Why does DROP TABLE-ing the `wp_options` reset my user session?
  94. How to refactor DB queries for better TTFB in WordPress?
  95. query sql-table and change entities
  96. Duplicate WP Migration affecting site on separate domain?
  97. Analog category_and (WP) in sql query
  98. Backtick (MySQL norm) added to SQL Server Query causing error
  99. SQL query to check whether a meta key is set or not for a post in post_meta table
  100. This SQL request call all time and overload my server : SELECT meta_value FROM wp_sitemeta WHERE meta_key = ‘wp_installer_network’ AND site_id = 1

Leave a Comment

tech