Feb 202014
 

The relationships between different tables that use the InnoDB storage engine in MySQL may be enforced by means of  Foreign Key Constraints.

These constraints make the database reject operations that would result in data inconsistencies. This post explains the possibilities offered by foreign key constraints, and how to implement them. Continue reading »

 Posted by at 1:06 pm
May 292013
 

Sometimes, when working with large volumes of data, such as some geographical information databases, the disk space used can become an issue. To reduce the disk space requiered to hold this data, MySQL can use compressed tables. Although compression is available for both InnoDB and MyISAM tables, the compression ratio achieved with MyISAM is much higher.

This post explains how to setup table compression for tables using MyISAM storage engine.

Continue reading »

 Posted by at 3:55 pm
May 262013
 

Ever since the first version of the MySQL, DBAs working with this DBMS have found, at some point, this issue: the ibdata1 file keeps growing indefinitely, until it occupies an excessive amount of the available disk space.

This article explains some possible actions that can be taken to eliminate or at least reduce the magnitude of this annoying problem.

Continue reading »

 Posted by at 7:46 am
May 162013
 

In a production server, a periodic backup of  the existing MySQL databases is usually done using mysqldump.

But there may be one of several users with different access permissions to those databases, the tables they hold, or even specific fields in the tables, and also to the stored procedures. This information also needs to be saved, to be able to recover all the functionality of the database.

This post presents a series of SQL sentences that can be used to save this information into backup files, and to recover it from the files into the database.

Continue reading »

 Posted by at 7:05 pm
Apr 062013
 

Regular expressions are a powerful tool available in many programming languages to check if a text string matches a pattern. In MySQL, regular expressions can be used as well in the WHERE clause of a SQL sentence.

This post comments on the possibilities, the syntax and the special considerations to be taken into account in using regular expression in MySQL, with practical usage examples.

Continue reading »

 Posted by at 12:01 pm
Feb 242013
 

We have already explained in a previous post how to connect to and work with a MySQL database in PHP. The examples in that post use the “mysql” PHP extension, which is compatible with older versions of PHP.

But, since PHP 5.0, the new “mysqli” (MySQL Improved) extension is available, with more functionality than the previous one. This post is about the new functionality implemented in mysqli, and includes a collection of code samples to connect, query and modifiy a mysql database with this new extension.

Continue reading »

 Posted by at 5:27 pm