数据库表内容删除

时间：2010-03-16 来源：benxiong

通常删除表中的内容，可以使用如下命令 SQL> delete from tbl_name;                    删除表中所有数据，但效率比较慢 SQL> TRUNCATE tbl_name;                       删除表中所有数据，重建表结构，比较快 SQL> delete * from tbl_name where 条件 oder by [asc|desc] limit 数量   满足条件的记录排序后，删除指定数量的条数例如，删除nagios_servicechecks表中的日期为2010-01-07的记录，方法如下： mysql> desc nagios_servicechecks;
+-----------------------+---------------+------+-----+---------------------+----------------+
| Field                 | Type          | Null | Key | Default             | Extra          |
+-----------------------+---------------+------+-----+---------------------+----------------+
| servicecheck_id       | int(11)       | NO   | PRI | NULL                | auto_increment |
| instance_id           | smallint(6)   | NO   | MUL | 0                   |                |
| service_object_id     | int(11)       | NO   | MUL | 0                   |                |
| check_type            | smallint(6)   | NO   |     | 0                   |                |
| current_check_attempt | smallint(6)   | NO   |     | 0                   |                |
| max_check_attempts    | smallint(6)   | NO   |     | 0                   |                |
| state                 | smallint(6)   | NO   |     | 0                   |                |
| state_type            | smallint(6)   | NO   |     | 0                   |                |
| start_time            | datetime      | NO   | MUL | 0000-00-00 00:00:00 |                |
| start_time_usec       | int(11)       | NO   |     | 0                   |                |
| end_time              | datetime      | NO   |     | 0000-00-00 00:00:00 |                |
| end_time_usec         | int(11)       | NO   |     | 0                   |                |
| command_object_id     | int(11)       | NO   |     | 0                   |                |
| command_args          | varchar(255) | NO   |     |                     |                |
| command_line          | varchar(255) | NO   |     |                     |                |
| timeout               | smallint(6)   | NO   |     | 0                   |                |
| early_timeout         | smallint(6)   | NO   |     | 0                   |                |
| execution_time        | double        | NO   |     | 0                   |                |
| latency               | double        | NO   |     | 0                   |                |
| return_code           | smallint(6)   | NO   |     | 0                   |                |
| output                | varchar(255) | NO   |     |                     |                |
| long_output           | varchar(8192) | NO   |     |                     |                |
| perfdata              | varchar(255) | NO   |     |                     |                |
+-----------------------+---------------+------+-----+---------------------+----------------+
23 rows in set (0.00 sec) mysql>delete from nagios_servicechecks where left(end_time,10)= '2010-01-07'