Category: MySql

Help and howto do things in MySQL

MySQL is the world’s most popular open source database, recognized for its speed and reliability, and is frequently used with PHP to create websites. The Electric Toolbox website uses MySQL as its database backend, and I have personally been using MySQL since 1999. In that time it has proven itself to me to be an extremely fast and reliable database management system, although the earlier versions lacked some features found in other database systems, such as stored procedures, triggers, sub selects and joined table updates. Most of these are available from the 4.1 and 5.0 releases.

If you are looking for MySQL databases – for example geographical databases, I suggest you take a look at SQL Dumpster

Example table for MySQL

In my MySQL posts I’ve tended to use fruit in my example tables, so in this post will standardize the table structure and data that appears in future examples. Those future posts will refer back to this post for the table structure and base data.

Read More »

Load data into MySQL with foreign key constraint issues Part 1

One of the MySQL databases I work with has a couple of hundred INNODB tables set up with foreign key constraints. I needed to copy the database using mysqldump to export data from one server and then load that into another but kept getting foreign key constraint errors. This post looks at how to load data from a file with MySQL and ignore foreign key constraints.

Read More »

MySQL: Using IF in a WHERE clause

I recently needed to use an IF statment in a WHERE clause with MySQL. This isn’t the most ideal situation and should probably be avoided normally but we needed to do it for one reason or another and this post shows how to do it.

Read More »

MySQL utility commands

This post is a summary of MySQL posts from this blog relating to MySQL utility commands, such as describing table structure, dropping columns from a table, and so on; and server settings.

Read More »

String concatenation with MySQL – Part 2

A few months ago I posted about string concatenation with MySQL using the CONCAT function and in this follow up post look at the CONCAT_WS function which glues the fields together with the specified string. CONCAT_WS means CONCATenate With Separator i.e. join the fields together with a specified separator string.

Read More »

Auto increment a value on the fly with MySQL

By assigning a value to a variable in MySQL and incrementing the variable as part of a select statement, it’s possible to have anumber auto-incremented on the fly. This number has no other reference to the table other than the order the data is selected . It is also possible to auto increment the variable when running an update statement so a new set of incrementing values could be stored into the table.

Read More »