Common pitfalls to avoid in MySQL - Part-1

Well over the years while I have been working with MySQL database, there are some aspects (which might be well known to some) that cost me a lot of time to figure out when I cam across them for the first time. So thought I would list some of those aspects here. So that it might become a kind of reference point for me and also someone else can benefit from it.

Please Note : All I have written and executed here in this article are done on MySQL version 5.0.27 using the default settings that comes when you install. People are welcome to comment and suggest if any of these can be overcome by any changes in configuration or settings. Also if any of this has changed in the newer versions, please feel free to comment.

I will be using 2 tables for my examples.
First is the employee table as shown below
 Second is the employee communication table as shown below

1. Count on columns

Count on a column name which has null values will leave out that row during the count.

On executing the below query the result is 5

SELECT COUNT(id) FROM employee

Select all Open in new window

Where as the same query when done on the "lastname" column instead of the "id" column as shown below will result the count as 4

SELECT COUNT(lastName) FROM employee

Select all Open in new window

The reason being for the employee with id 2, the "lastname" is null hence the count leaves out that row while counting.

This may be a good feature for some, but if this is not known, your reports / queries may go wrong if the count is put on a nullable column and the some of the data has null values.  Hence it is always better to put the count on the primary key or a not null column.

2. Concat function

While using the concat function in MySQL, care should be taken that none of the column or values being used as part of the concat should not be NULL else the entire value becomes null.

For example taking the same table in the point 1 above,

SELECT id, CONCAT(firstName,' ',lastName) AS fullname FROM employee

Select all Open in new window

will result as shown below because the last name of employee with id 2 is null.
 Hence if you are not very sure of the values in the column, it is better to use either IFNULL or COALESCE as shown below

SELECT id, CONCAT(IFNULL(firstName,''),' ',COALESCE(lastName,'') ) AS fullname FROM employee 

Select all Open in new window

3. Group by

This is one of the most strange behavior of MySQL that I have seen. If you coming from any other RDBMS' like Oracle , MSSQL, Postgres, etc. you are in for a surprise.  

While using any aggregate function and selecting any other column along with it, the norm is to include it in the group by, but here in MySQL it allows you to select any number of columns without it being in the group by.

It does expect at least one group by, but after that it does not prompt an error even if there are more selects than group by.

Like in the query below I am selecting an additional column "batType" which is not in the group by.

SELECT gender,batType, COUNT(id) FROM employee GROUP BY gender

Select all Open in new window

On executing this, you usually expect an error but it runs and gives the below output which is wrong
the actual query and the result that you expect is as below

SELECT gender,batType, COUNT(id) FROM employee GROUP BY gender,batType

Select all Open in new window

which results

Hence be careful while having multiple selects with aggregate function and not mentioned in the group by, it will not throw an error but will give you wrong results.

4. Data truncation while inserting

This again will surprise people coming from other RDBMS'. When you are inserting any data into a varchar field of a fixed length and the data being inserted is larger than what is defined as the property of the column, MySQL inserts the data by truncating it to the required length and just gives out a warning and no errors.

For example check the below insert statement, the value being entered for "firstName" is total of 70 bytes where the column type is varchar(50).

INSERT INTO employee (id,firstName,lastName,gender,batType) VALUES (6,'This is a long data that will be truncated and inserted into the table','Last name','M','R');

Select all Open in new window

On executing this, there are no errors only one warning and the result is as below

So if you are not careful, you may have incomplete data stored in the db without you knowing it.

5. Auto-casting of data

MySQL auto-cast data without giving an error
One example of this is that , see the below statement to insert a record into the employee table

INSERT INTO employee (id,firstName,lastName,gender,batType) VALUES ('test','R','Ashwin','M','R');

Select all Open in new window

Normally you expect it to throw an error because column "id" is of type int and I am trying to insert a string "test" into it. But what it does is that without throwing an error it inserts that data as shown below.
The value for "id" it has inserted 0.

This care has to be taken even while passing values to a stored procedure or while comparing two values.


That is it for now, will add on more as and when I face anything new. Hope this has helped you, if it has do let me know by commenting or clicking "yes" below.

Instead of adding more here, I have added second part to this topic, you can view it at Common pitfalls to avoid in MySQL - Part-2

Thanks!!!