Make MySQL Auto-Incrément ID (Re) Démarrer de 1

En supposant qu'il n'y ait pas de problèmes clés étrangers à traiter, ce code le fera:

SELECT MAX(ID) + 1 FROM MYTABLE; -- note the output
ALTER TABLE MYTABLE AUTO_INCREMENT = <result from above>;

 update table_name set id =NULL; alter table table_name change column
 `id` `id` int auto_increment;

ALTER TABLE event AUTO_INCREMENT = 1;

    function backup_tables($host, $user, $pass, $dbname, $tables = '*'){

    $connect = mysqli_connect($host, $user, $pass , $dbname);

     mysqli_query($connect, "SET NAMES 'utf8'");


    //get all of the tables
    if($tables == '*'){
        $tables = array();
        $result = mysqli_query($connect, 'SHOW TABLES');
        while($row = mysqli_fetch_row($result))
        {
            $tables[] = $row[0];
        }
    }
    else
    {
        $tables = is_array($tables) ? $tables : explode(',',$tables);
    }

    foreach($tables as $table){
        $table = trim($table);
    // getting all table fields
    $tblDetails = mysqli_query($connect,"SHOW FULL COLUMNS FROM $table");

    // we may need to know how to create our table
    $tblCreate = mysqli_fetch_row(mysqli_query($connect, 'SHOW CREATE TABLE '.$table));

    // getting last line from table creation script in order to get info about engine ->suffix1
    $suffix1 = end(explode(PHP_EOL,$tblCreate[1]));

        // if there is auto increment we have to remove
        if (strpos($suffix1,"AUTO_INCREMENT")){ 
            $tmpArr = explode(" ",$suffix1);
            $newStr = '';
            foreach ($tmpArr as $term){
                if (!is_int(strpos($term, "AUTO_INCREMENT"))) $newStr .= $term . ' '; else $suffix4 = $term; // suffix4 stores next value of auto_increment 
            }
            $suffix1 = $newStr;
        } // now if there is auto_increment we removed from the last line of creation table script


        $return .= "DROP TABLE IF EXISTS `".$table."` CASCADE;\n\n";

     // starting creation table with our rules
        $kgbReturn = "CREATE TABLE `$table` (\n";


        while($cols = mysqli_fetch_row($tblDetails )){

            if ($cols[2]) $cols[2] = " COLLATE " . $cols[2]; //if a charset defined add to line
            if ($cols[3]=='NO') $cols[3] = " NOT NULL"; // if the field may be null 

            $kgbReturn .= "`".$cols[0]."` ".$cols[1]. $cols[2] . $cols[3]. ",\n"; //field creation line ready
        }


        $kgbReturn = rtrim($kgbReturn,",\n") . "\n" .trim($suffix1," ") . ";\n\n"; // table creation without auto_increment


        $tblDetails = mysqli_query($connect,"SHOW FULL COLUMNS FROM $table WHERE (`Key` LIKE 'PRI%')");
        $suffix2 = '';
        while($cols = mysqli_fetch_row($tblDetails )){
            $suffix2 .= "ALTER TABLE `". $table ."` \n ADD PRIMARY KEY (`".$cols[0]."`);\n\n";
        }


        $tblDetails = mysqli_query($connect,"SHOW FULL COLUMNS FROM $table WHERE (Extra LIKE 'auto_increment%')");
        $suffix3 = '';
        while($cols = mysqli_fetch_row($tblDetails )){
            $suffix3 = "ALTER TABLE `". $table ."` \n ADD PRIMARY KEY (`".$cols[0]."`);\n\n";
            $suffix3 = "ALTER TABLE `".$table."` \n MODIFY `".$cols[0]."` ".$cols[1]." NOT NULL AUTO_INCREMENT, ".$suffix4.";";
        }






        $return .= $kgbReturn;

        $result = mysqli_query($connect, 'SELECT * FROM '.$table);
        $num_fields = mysqli_num_fields($result);

        // insert into all values 
        for ($i = 0; $i < $num_fields; $i++){

            while($row = mysqli_fetch_row($result)){

                $return .= 'INSERT INTO '.$table.' VALUES(';

                for($j=0; $j < $num_fields; $j++){
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = str_replace (array("\r\n", "\n", "\r", PHP_EOL), '\r', $row[$j])
    ;

                    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return .= '""'; }
                    if ($j < ($num_fields-1)) { $return .= ','; };
                }
                $return .= ");\n";
            }
        }
        $return .= "\n\n"; // insert values completed. 


        // now add primary key and auto increment statements if exist 
        $return .= $suffix2 . $suffix3 . "\n\n\n";  


        echo "<pre>".$return ."</pre>"; // debug line. comment if you don't like.


    }


    // we need to write to a file that coded as utf-8
    $bkTime = date('Y_m_j_H_i_s');
    $fileName = 'backup-db-'.$bkTime.'.sql';
    $f=fopen($fileName,"w"); 
    # Now UTF-8 - Add byte order mark 
    fwrite($f, pack("CCC",0xef,0xbb,0xbf));

    fwrite($f,$return); 
    fclose($f); 


    }