Truncate Table via Command Line in Linux

truncate table via command line in Linux

You can use mysql command line client to do it

mysql -h dbserver_hostname -e "truncate table schema_name.table_name"

Truncate a specific table from a MySQL database using Command Line

The commandline has trouble with the dashes in the db and tablenames.

You can however access the client and then send your statement, have to put the db and table in backticks.

mysql -u root -h localhost
truncate table `site-local`.`cloud_securities`;

edit: nvm, you can do it from the commandline by using backticks, but you have to escape them.

Solution:

mysql -u root -p'' -h localhost site-local -e "truncate table \`site-local\`.\`cloud_securities\`"

shell script to truncate all MySql tables

How about something cheeky like this:

mysqldump  --no-data mydb | mysql mydb

Gets a dump of the schema and replays it into the database!

Alternatively, check out mk-find in Maatkit, you should be able to do something like this:

mk-find -exec "truncate %s"

Description of mk-find:

This tool is the MySQL counterpart to
the UNIX ‘find’ command. It accepts
tests (such as “find all tables larger
than 1GB”) and performs actions, such
as executing SQL (”DROP TABLE %s”).
With this tool at your disposal you
can automate many tedious tasks, such
as measuring the size of your tables
and indexes and saving the data for
historical trending, dropping old
scratch tables, and much more. It is
especially useful in periodic
scheduled tasks such as cron jobs.

How to truncate extraneous output using shell script?

Square brackets are special to sed. Simply escape them.

s/var songs = \[//g

If you use slash / as the regex delimiter, it becomes special. Either escape it or use a different delimiter.

s/}]; <ol class="songs tracks"><\/ol>//g
s|}]; <ol class="songs tracks"></ol>||g

How do I truncate tables properly?

Plan A:

SET FOREIGN_KEY_CHECKS = 0; -- Disable foreign key checking.
TRUNCATE TABLE forums;
TRUNCATE TABLE dates;
TRUNCATE TABLE remarks;
SET FOREIGN_KEY_CHECKS = 1; -- Enable foreign key checking.

Plan B:

You should truncate child tables firstly, then parent tables.

Disabling foreign key checks risks entering rows into your tables that don't adhere to the constraints which can cause undefined behavior.

Truncate multiple tables in one MySQL statement

No, you can only truncate a single table with TRUNCATE command. To truncate multiple tables you can use T-SQL and iterate through table names to truncate each at a time.

DECLARE @delimiter CHAR(1),
        @tableList VARCHAR(MAX),
        @tableName VARCHAR(20),
        @currLen INT

SET @delimiter = ','

SET @tableList = 'table1,table2,table3'

WHILE LEN(@tableList) > 0
BEGIN
    SELECT @currLen = 
    (
        CASE charindex( @delimiter, @tableList ) 
            WHEN 0 THEN len( @tableList  ) 
            ELSE ( charindex( @delimiter, @tableList  ) -1 )
        END
    ) 

    SELECT @tableName = SUBSTRING (@tableList,1,@currLen )

    TRUNCATE TABLE @tableName

    SELECT tableList = 
    (
        CASE ( len( @tableList ) - @currLen  ) 
            WHEN 0 THEN '' 
            ELSE right( @tableList, len( @tableList ) - @currLen  - 1 ) 
        END
    ) 
END

You can have all your table names comma separated in @tableList variable and yes you can truncate multiple tables from different schemas if they are prefixed.

Truncate all tables in MySQL database that match a name pattern

Use concat:

SELECT concat('TRUNCATE TABLE `', TABLE_NAME, '`;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'inventory%'

This will of course only generate SQL which you need to copy and run yourself.

---

Related Topics