php 如何在laravel用户删除中重置自动增量？

How to reset auto increment in laravel user deletion?

提问by aimiliano

I have been struggling to find out a way to reset the auto increment value in Laravel 4 but it seems that this functionality is not embedded in laravel 4 at least for now. so i did it this way:

我一直在努力寻找一种方法来重置 Laravel 4 中的自动增量值，但似乎这个功能至少目前没有嵌入到 Laravel 4 中。所以我是这样做的：

$user = User::find($user_id);

                if ($user)  {
                    if ($user->delete()){

                    DB::statement('ALTER TABLE users AUTO_INCREMENT = '.(count(User::all())+1).';');

                    echo json_encode('User Was Deleted Successfully..');
                    }   
            }

each time i delete a user from the database i set the auto increment pointer to the number of all users +1.

每次我从数据库中删除一个用户时，我都会将自动增量指针设置为所有用户的数量+1。

if anybody has a better solution inform me please..

如果有人有更好的解决方案请告诉我..

回答by Abhishek Saini

Like everyone else replied, there is not really a need to move the counter back when you delete a row. You can however truncatea table which will delete all the table rowsand reset the counter.

就像其他人回答的那样，删除行时实际上不需要将计数器移回。但是，您可以创建truncate一个将删除所有表行并重置计数器的表。

You cannot truncatea table that has Foreign Key Constraintsapplied on it (truncateis not the same as deletewhich simply deletes all the rows while keepingthe auto-increment counter.).

您不能truncate对Foreign Key Constraints应用了它的表（truncate与delete在保留自动递增计数器的同时删除所有行不同。）。

Hence, while using foreign key constrains, MySQL might stop you from truncating a table which has foreign key constraintsapplied to it.

因此，在使用 时foreign key constrains，MySQL 可能会阻止您截断已foreign key constraints应用于它的表。

You can perform the following steps to achieve what you want, but beware, there may be a risk to your data integrity. I only use it for my testing purposes.

您可以执行以下步骤来实现您想要的效果，但请注意，您的数据完整性可能存在风险。我只将它用于我的测试目的。

1. Edit the DatabaseSeederclass (which is available at app/database/seeds/DatabaseSeeder.php) as follows:

   <?php
   class DatabaseSeeder extends Seeder {
       /**
       * Run the database seeds.
       *
       * @return void
       */
       public function run()
       {
           Eloquent::unguard();
   
           // Disable Foreign key check for this connection before running seeders
           DB::statement('SET FOREIGN_KEY_CHECKS=0;');
   
           $this->call('UserTableSeeder');
           // ...
   
           // FOREIGN_KEY_CHECKS is supposed to only apply to a single
           // connection and reset itself but I like to explicitly
           // undo what I've done for clarity
           DB::statement('SET FOREIGN_KEY_CHECKS=1;');
       }
   }
   

2. Now the Table Seeder classes (Example, UserTableSeederin this case, which should be created at app/database/seeds/UserTableSeeder.php) can call truncate table(s) as follows:

   <?php
   class UserTableSeeder extends Seeder {
   
       public function run()
       {
           // Truncate the table.
           DB::table('users')->truncate();
   
   
           // The auto-increment has been reset.
           // Now we can start adding users.
           User::create(
               array(
                   'email' => '[email protected]',
                   'password' => Hash::make('test')
               )
           );
       }
   }
   

1. 编辑DatabaseSeeder类（可在 中找到app/database/seeds/DatabaseSeeder.php）如下：

   <?php
   class DatabaseSeeder extends Seeder {
       /**
       * Run the database seeds.
       *
       * @return void
       */
       public function run()
       {
           Eloquent::unguard();
   
           // Disable Foreign key check for this connection before running seeders
           DB::statement('SET FOREIGN_KEY_CHECKS=0;');
   
           $this->call('UserTableSeeder');
           // ...
   
           // FOREIGN_KEY_CHECKS is supposed to only apply to a single
           // connection and reset itself but I like to explicitly
           // undo what I've done for clarity
           DB::statement('SET FOREIGN_KEY_CHECKS=1;');
       }
   }
   

2. 现在 Table Seeder 类（示例，UserTableSeeder在本例中，应在 处创建app/database/seeds/UserTableSeeder.php）可以调用 truncate table(s) 如下：

   <?php
   class UserTableSeeder extends Seeder {
   
       public function run()
       {
           // Truncate the table.
           DB::table('users')->truncate();
   
   
           // The auto-increment has been reset.
           // Now we can start adding users.
           User::create(
               array(
                   'email' => '[email protected]',
                   'password' => Hash::make('test')
               )
           );
       }
   }
   

回答by Yamakenji

use Illuminate\Support\Facades\DB;

public function refreshDB()
{
    $max = DB::table('users')->max('id') + 1; 
    DB::statement("ALTER TABLE users AUTO_INCREMENT =  $max");
}

// Note: This solution is for resetting the auto_increment of the table without truncating the table itself 

回答by Joeri

I don't know if it's smart or not, but this will cleanup your table.

我不知道它是否聪明，但这会清理你的桌子。

public function cleanup($table_name)
{       
    DB::statement("SET @count = 0;");
    DB::statement("UPDATE `$table_name` SET `$table_name`.`id` = @count:= @count + 1;");
    DB::statement("ALTER TABLE `$table_name` AUTO_INCREMENT = 1;");
}

MySQL will set the AUTO_INCREMENT to last+1
If you've set your foreign keys to ON UPDATE CASCADE the children will know about the changes and cascade the update.

MySQL 会将 AUTO_INCREMENT 设置为 last+1
如果您已将外键设置为 ON UPDATE CASCADE，孩子们将知道更改并级联更新。

This stuff takes server time and gives you little in return. I think that's why you're getting loads of "don't waist your time" responses? For a count you should use ->count() and not the last id.

这些东西需要服务器时间，但回报很少。我认为这就是为什么您会收到大量“不要浪费时间”的回复？对于计数，您应该使用 ->count() 而不是最后一个 id。

I also don't know if the statements should be within a transaction to prevent errors when users are added while your statements are running.

我也不知道语句是否应该在事务中以防止在语句运行时添加用户时出错。

回答by pableiros

If you are using PostgreSQL:

如果您正在使用PostgreSQL：

public function resetAutoincrement()
{
    $max = DB::table('users')->max('id') + 1;
    DB::statement('ALTER SEQUENCE users_id_seq RESTART WITH ' . $max);
}