Category Archives: MySQL

What does int(11) means in MySQL?

A very common misconception about what int(11) means in MySQL is that the column can store maximum integer value with 11 digits in length. However, this is not true. int(11) does not determines the maximum value that the column can store in it. 11 is the display width of the integer column, unlike the characters columns where the number means number of character that can be stored.
Read more »

How to get size of BLOB in MySQL

Recently I wanted to get the size of the data stored in the BLOB field of a MySQL table. BLOB is a field which can be used to store variable amount of data. There is a simple MySQL String function, to find the size of BLOB data, OCTET_LENGTH. This function returns the length of BLOB in bytes. You can also use LENGTH function, because OCTET_LENGTH is synonym for LENGTH.

Sample usage:
Suppose in a table test_blob, there is a BLOB column (blob_content). We want to find the length of the BLOB field where id = 1.

SELECT OCTET_LENGTH(blob_content) FROM test_blob WHERE id = 1
I prefer to use OCTET_LENGTH, instead of LENGTH because the function name implies that I would get the result in terms of bytes and not characters.
Related Links:

MySQL ordering results by specific field values

In MySQL we can sort the results in ascending or descending order very easily by using the ORDER BY clause. However, there are times when you want to sort the results in a specific order which cannot be done using the ASC or DSC. FIELD() of MySQL ORDER BY clause can be used to sort the results in a specific order.
For this post I will use my example MySQL table and data. This is just a sample table with sample data, used to represent what I need. So, in this table I have different species of pets and I need to get a list of all pets in the order dogs, cat, snake and bird. I cannot use the MySQL ORDER BY clause to fetch the results in this order. By using the FIELD() function of the MySQL ORDER BY clause I can get the results in the order I need. Here is the query I can use.
SELECT name, species FROM `pet`
ORDER BY FIELD(species, 'dog','cat','snake','bird'), name ASC

Read more »

Sample MySQL table

For my MySQL posts here is a sample MySQL table that I would be using for my examples. I would refer my posts back to this for table structure and sample data.
Table structure:

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
Create Table SQL:

CREATE TABLE pet (
	name VARCHAR(20),
	owner VARCHAR(20),
	species VARCHAR(20),
	sex CHAR(1),
	birth DATE,
	death DATE
);
Sample Data:

name owner species sex birth death
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird 1997-12-09
Slim Benny snake m 1996-04-29
Insert sample data SQL:

INSERT INTO pet VALUES
('Fluffy','Harold','cat','f','1993-02-04',''),
('Claws','Gwen','cat','m','1994-03-17',''),
('Buffy','Harold','dog','f','1989-05-13',''),
('Fang','Benny','dog','m','1990-08-27',''),
('Bowser','Diane','dog','m','1979-08-31','1995-07-29'),
('Chirpy','Gwen','bird','f','1998-09-11',''),
('Whistler','Gwen','bird','','1997-12-09',''),
('Slim','Benny','snake','m','1996-04-29','')
I have taken this table structure and data from MySQL documentation (Creating a table and Loading Data into Table).

How to Find and Replace Data in MySQL

Recently, while migrating my blog, I had to find all the occurrences of my old URL and replace it with my URL. One way of doing this was to get a database dump, open it in a text editor and the do a find replace and the import it back. This is a 4 step process. However, I found that we can do this only in 1 step directly in MySQL using the UPDATE statement with REPLACE function.

Read more »