Category Archives: MySQL

MySQL: Deactivate Referential Integrity

If you database has InnoDB tables, you will need to deactivate referential integrity while restoring data. Unfortunately, this can’t be done using the mysqldump utility. To do so, backup your database as you would normally. When done, open the SQL … Continue reading

Posted in MySQL | Leave a comment

MySQL Optimization

use ENUM whenever possible do not be too generous with the varchar length NULL columns take more space and make computation harder – avoid using them, use empty strings or zeros. InnoDB-specific. Use a surrogate key which is a primary … Continue reading

Posted in MySQL | Leave a comment

MySQL – Importing Many SQL Dump Files in a Database

ls *.sql | awk ‘{printf(“mysql -u [username] -p[password] [databasename] < %s\n”, $1) | “/bin/sh” }’

Posted in Linux Command Line, MySQL | Leave a comment

MySQL: Time Intervals (Last x Days/Weeks/…)

Here is an example that uses date functions. The following query selects all rows with a date_col value from within the last 30 days: SELECT something FROM tbl_name WHERE date_col >= DATE_SUB(CURDATE(),INTERVAL 30 DAY) ; ////////////////////////////////////////////////////////////////////////////////////////////////// – – current week … Continue reading

Posted in MySQL | Leave a comment

MySQL Cheatsheet

ALTER TABLE ALTER TABLE table_name CHANGE column_name new_column_name int(11) NOT NULL DEFAUL 0; ALTER TABLE table_name MODIFY column_name column_type; ALTER TABLE table_name ADD column_name varchar(16) NOT NULL AFTER column_name2; ALTER TABLE table_name ADD column_name varchar(32) AFTER column_name2 ALTER TABLE table_name … Continue reading

Posted in MySQL | Leave a comment

Something about Stored Procedures

Before enter a stored procedure you must change the delimiter for the instruction this way (for example): Delimiter $ SHOW PROCEDURE STATUS will show you all the existing stored procedures SHOW CREATE PROCEDURE procedure_name will show you the content of … Continue reading

Posted in MySQL | Leave a comment