Using MySQL Stored Procedure To Create Sample Data

MySQL stored procedures are programs that are stored and can be executed on the MySQL server. Using MySQL Stored Procedure you can solve mysql related problem or task easily. Here I’m describing a stored procedure that I used to create some sample data. Read Full Article

MySQL Random Data Selection

Some days ago I was working in a vocabulary game and dictionary. The dictionary contains 1,10,000 words and meanings. I developed a vocabulary game where I had to randomly choose 10 words out of 1,10,000 dataset. Here I’m describing the possible solutions for retrieving 10 random words from 1,10,000…  Read Full Article

MySQL query that get ranks today, this week and this month

In this article I’m showing how to retrieve result based on today, week and month using mysql query. To learn the techniques just visit http://thinkdiff.net/mysql/getting-rank-today-this-week-and-this-month/

Getting user’s rank using mysql query

In this article you’ll learn how to get user’s rank in mysql query. Check the link http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query

Books

Some helpful programming/web/technical related books are here Books

Encrypt MySQL data using AES techniques

Learn how to encrypt/decrypt MySQL data using AES techniques by the following article Encrypt MySQL data using AES techniques

MySQL – NULL vs ” vs ‘NULL’

Today, in one of my project I’ve to check empty fields.

The field name is: answer it’s type is TEXT and Default value is NULL

In my SQL query I was checked at first answer != NULL (if the answer is not null, i.e if the answer is not empty), But this was not showing the correct result.

Then I changed it to answer != ” ( i.e ” means empty string) then it showed the correct result.
Then I test with this answer != ‘NULL’, and it also showed the correct result.

MySQL Workbench – Next-generation visual database design application

Visual database design is very essential. Almost every web application needs database. And by designing visual representation, relation of tables makes an developer easy to understand on the database. There are many tools to design database like DBDesigner, SQLYog. Recently I found another application that is called MySQL Workbench. It can be used to efficiently design, manage and document database schemata. After using it’s functionality, I found this is a really good tool for design database specially for MySQL.

MySQL – The GROUP_CONCAT() function

GROUP_CONCAT() function is used to concatenate column values into a single string. It is very useful if you would otherwise perform a lookup of many row and then concatenate them on the client end.

For example if you query:

mysql> SELECT Language FROM CountryLanguage WHERE CountryCode = 'THA';

It outputs:

Language

Chinese

Khmer

Kuy

Lao

To concatenate the values into a single string, you query:

mysql> SELECT GROUP_CONCAT(Language) As Languages FROM CountryLanguage WHERE CountryCode = 'THA';

Then the output will be:

Languages

Chinese, Khmer, Kuy, Lao

You can also use some format of GROUP_CONCAT(). Like

• SELECT GROUP_CONCAT( Language SEPARATOR ‘-‘ )… It will use ‘-‘ instead of ‘,’
• SELECT GROUP_CONCAT( Language ORDER BY Language DESC )… To change the order and shorting output

One thing to remember: GROUP_CONCAT() ignores NULL values.

MySQL – Choose exact numeric data type

When we use a relational database system, we should know some important things. Numeric data type is very essential for a table. Here I describe some important points of Numeric Data type in MySQL:

MySQL has 9 numeric data types

• Integer:            TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
• Floating Point:  FLOAT, DOUBLE
• Fixed Point:      DECIMAL
• Other:              BIT, (ENUM maybe)‫‏‬

Some poor design:

• INT(1)‫‏‬
• BIGINT AUTO_INCREMENT
• no UNSIGNED used
• DECIMAL(31,0)‫‏‬

INT(1) – 1 does not mean 1 digit.   (1) represents client output display format only. INT is 4 Bytes. TINYINT is 1 Byte . TINYINT UNSIGNED can store from 0 – 255. BIT is even better when values are 0 – 1

BIGINT is not needed for AUTO_INCREMENT

• INT UNSIGNED stores 4.3 billion values
• You should be partitioning when at billions of rows
• BIGINT is applicable for some columns e.g. summation of values

Best Practice

• All integer columns UNSIGNED unless there is a reason otherwise.
• Adds a level of data integrity for negative values