Truncate all tables in a particular database using a single line command.

Generally if you like to truncate or empty all the records in a database containing more no. of tables, use the below command to do it. It really helps to remove all the records completely only having the structure of the tables left in a database.

mysql -u {user-name} -p {database-name} -BNe "show tables" | awk '{print "TRUNCATE TABLE " $1 ";"}' | mysql -u {user-name} -p {database-name}

Let me know if it doesn't works you out.

Dumping Multiple Selected MySQL Tables as SQL

Not sure if anybody already figured out how to dump the selected mysql tables from a particular database. But after googling I found some way which helped me how to proceed and adding my thought little & attained what I need. Ok, not bugging you too much, here is my requirement:

I need to dump 5 tables (employee, address, salary, attendance, payslip) & the tables whose name starts with map_* in a single command.

Solution
What I know from the syntax is that I can append as many table name with the mysqldump to get the dump of all the tables.mysqldump -u{user} -p {database} {table1} {table2} {table3} > {destination}.sql
but what here missing is that I can add map_* tables which is really important for me to proceed further.

As said, after googling I got the site and understood how to get map_* tables include with the mysqldump.

The referred site provided me something like this..
mysql databasename -u [root] -p[password] -e 'show tables like "wp_153_%"' | grep -v Tables_in | xargs mysqldump [databasename] -u [root] -p[password] > [target_file]

but what I modified to get my requirement complete is
mysql {databasename} -u {root} -p{password} -e 'show tables like "wp_153_%"' | grep -v Tables_in | xargs mysqldump {databasename} -u {root} -p{password} {table1} {table2} {table3} > {destination}.sql

So my final output was
mysql employeeDB -u root -p -e 'show tables like "map_%"' | grep -v Tables_in | xargs mysqldump employeeDB -u root -p employee address salary attendance payslip > destination.sql

Hope NO other better option than this.


Note: I have remove {password} from my command as I think we should not execute the password in the command line instead asking us

ER Diagram Tool for Mac

Have you ever did an ER Diagram for the Tables ? hmm, I hope yes, you might have did in your schoolings. Did you thought at that time how much it will be useful later ? Ok, Ok, coming straight to the point, I was figuring out any tool which might do the ER Diagram easily without straining too much. Atlast I found one for Mac.

SQLEditor for Mac

I have downloaded the 30 Day trial and tested it and works fine. I was in search for this which accepts the MySQL exported SQL file and it draws the ER Diagram for it.

Mysql Number Formating '0000X'

I was just trying to figure out how to print an stored value of integer having 0 pre appended with it.

The best and good way to prepend zero integer value for a integer of fixed size is to alter the table having "UNSIGNED ZEROFILL" as an attribute for the field which needs zero prepended with it.


CREATE TABLE IF NOT EXISTS `table_1` (
`ID` int(11) NOT NULL auto_increment,
`some_id` int(6) unsigned zerofill NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8321 ;


You can do when data is present and you want to do this.


ALTER TABLE `table_1` CHANGE `some_id` `some_id` INT( 6 ) UNSIGNED ZEROFILL NOT NULL


feel free to express your thoughts too.