How to Truncate Table using CakePHP?

I want to truncate my Database T开发者_高级运维able, with CakePHP Model, I have used $this->Model->deleteAll code for the same, and it works fine.

Now, What i want is, next time whenever my new records are inserting, it should start ID with 1 only, which does not work with deleteAll function, so Is there any default CakePHP Syntax to make table Truncate ?

Let me know !

NOTE: This answer is valid only up to CakePHP 1.3. I haven't used this on the never versions so I've no idea whether it works.

deleteAll only deletes the data, it does not truncate the table.

You'll need to call the query() method.

$this->Model->query('TRUNCATE TABLE table_name_in_mysql;')

http://book.cakephp.org/view/1027/query

@JohnP's doesn't take into account the table's prefix as configured in database.php. Here's a slightly more robust approach.

The DboSource object attached to each model already has a fullTableName() method that does exactly what we need.

First, create Model/AppModel.php if it doesn't already exist, and add this method to it:

/**
 * fullTableName
 *
 * Provides access to the Model's DataSource's ::fullTableName() method.
 * Returns the fully quoted and prefixed table name for the current Model.
 *
 * @access public
 * @param boolean $quote Whether you want the table name quoted.
 * @param boolean $schema Whether you want the schema name included.
 * @return string  Full quoted table name.
 */
public function fullTableName($quote = true, $schema = true) {
    $datasource = $this->GetDataSource();
    return $datasource->fullTableName($this, $quote, $schema);
}

With this, you can get the full table name, including the prefix, for any Model in your Cake app:

$this->Model->fullTableName();

We can do better though. Next, add this method to AppModel too:

/**
 * truncate
 *
 * Truncates ALL RECORDS from the Model it is called from! VERY DANGEROUS!
 * Depends on the ::fullTableName() method to concatenate the configured
 * table prefix and table name together and quote the whole bit properly.
 *
 * @access  public
 * @return  mixed
 */
public function truncate() {
    $fullName = $this->fullTableName();
    $q = 'TRUNCATE TABLE %s';
    return $this->query(sprintf($q, $fullName));
}

Now you can (easily, so be careful!) truncate any Model in your app like so:

$this->Model->truncate();

And if you ever need to adjust the SQL query to match a different DataSource, you can do so in a central place in your app. You can also easily override the truncate() method in specific models if they use a different DataSource with a different syntax.

A database driver agnostic solution for CakePHP 3:

Create an AppTable.php, and make all your tables extend this one.

Add this function to it:

public function truncate()
{
    $truncateCommands = $this->schema()->truncateSql($this->connection());
    foreach ($truncateCommands as $truncateCommand) {
        $this->connection()->query($truncateCommand);
    }
}

Then simply call $table->truncate(); and it should truncate the table regardless of which database driver you use.