Menu
I have got a simple MySQL table and primary index (id) is not numbered one by one (1, 31, 35, 100 etc.). I want them to be numbered like (1, 2, 3, 4). Please tell me how to do it. I would also like to point that I am aware of possible consequences of the operation, but I just want to tidy up the table.
622
A primary index is automatically created for the primary key and ensures that the primary key is unique. You can use the primary index to retrieve and access objects from the database. The unique index is a column, or an ordered collection of columns, for which each value identifies a unique row.
A primary key is unique, whereas an index does not have to be unique. Therefore, the value of the primary key identifies a record in a table, the value of the index not necessarily. Primary keys usually are automatically indexed - if you create a primary key, no need to create an index on the same column(s).
To add a new AUTO_INCREMENT integer column named c : ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (c); We indexed c (as a PRIMARY KEY ) because AUTO_INCREMENT columns must be indexed, and we declare c as NOT NULL because primary key columns cannot be NULL .
I agree other methods will work but I'm just giving a different idea. This will do without any temp table creation requirements::
SET @i=0;
UPDATE table_name SET column_name=(@i:=@i+1);
give a try to renumber method of dalmp (DALMP Database Abstraction Layer for MySQL using PHP.)
$db->renumber('table','uid');
basically it does this:
SET @var_name = 0;
UPDATE Tablename SET ID = (@var_name := @var_name +1);
ALTER TABLE tablename AUTO_INCREMENT = 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
