Some interesting tricks and features to work with MySQL

I think that each of you tried to find the own way to solve tasks in the learning process of a relational database management system, and not knowing that out there are various helpful features, which could speed up the queries at times and reduce the code size. In this article I want to share with you my experience, namely how to work with MySQL comfortably, often allowing the programmer to do the things that other databases would not be able to do. This material would be useful rather for those who just decided to delve into the wonderful world of queries, but maybe the experienced programmers will find something interesting for themselves here.

Removing duplicates

Very often, I have run into the questions on various specialized resources about how quickly and optimally to get rid of duplicate entries in the table. Immediately, it comes to mind that you need to create another table that is identical to original one, then create a unique key and copy the data into it from the original table, for example, you may use INSERT IGNORE. But there is an easier way. Simply you need to create a unique key in the table using this one:

ALTER IGNORE TABLE table1 ADD UNIQUE (field1, field2);
After you add the key all duplicates will be removed automatically.

Conversion of string to number

Let’s say you got to the task to find a home address in the table with a specific number. Moreover, the home number is stored in a text line num that contains values of ‘1‘, ‘1a’, ‘1/b‘, ‘2c’, ‘3q’, etc., and we want to find a home with a number consisting of 1 and some other characters. I think many would rush to use LIKE or REGEXP. But it will be easier to use the following MySQL feature:

FROM address
WHERE num + 0 = 1;

MySQL automatically sets all arguments to a numeric type if faced with an arithmetic operation. In case of the strings, all characters will simply be cut off starting from the first non-numeric.
This request will be done without any errors:

SELECT '1qwe3s' + '2regt3g';
As a result, we get the answer 3.

Using variables in the queries

Here is an example of solving the task using the variable.

Here is a table1:

It is needed to lead all fields and to add another 2, such as onStart and total.
total = summ — onStart.
onStart = total value of the previous entry, the first record onStart = 0.

Namely, we should get this result at the end:
455 2035

Using the variable we can solve the task and get rid of unnecessary JOINs and Subqueries:

SELECT, t1.summ, @i AS onStart, @i := t1.summ - @i AS total
FROM table1 t1
JOIN (SELECT @i := 0) var;

Counting the number of different entries in the table

Here is another common task. I am also giving an example.
Here is given the table 1 (id, f1, f2). We need to run a query that would return us the following result:
Total number of entries Number of entries with f1 = 1 Sum of values f2 for f1 = 2

Of course, you can get a result like this:

(SELECT COUNT(1) FROM table1 WHERE f1 = 1),
(SELECT SUM(f2) FROM table1 WHERE f1 = 2)
FROM table1;

Obviously, this is not the optimal solution. We have to run two additional subqueries for each entry. So we will do it differently:

SUM(f1 = 1),
SUM(IF(f1 = 2, f2, 0))
FROM table1;

Now everything looks fine. All we need, we counted at once in the table.

Column ‘id’ in group statement is ambiguous

In this article I want to draw your attention to an interesting MySQL feature.

We have a request:

FROM table1 t1
JOIN table2 t2 ON = t2.id_t1

It is seen that in GROUP BY block, we forgot to specify an alias for id field, and therefore, when we tried to run the query we got this error “Column ‘id’ in group statement is ambiguous”. It would seem that everything is correct. Now let’s change this request:

SELECT, t2.f1
FROM table1 t1
JOIN table2 t2 ON = t2.id_t1
We have removed from the list of led fields and the request worked, the data were grouped by Other relational database management systems, such as MS SQL or PostgreSQL could have given the error in the second case, but the second request for MySQL is absolutely correct.
So I encourage you to be more careful and always use aliases before the fields, otherwise you could get the error when you slightly change the request.

Data search for the last date

Finally, I want to give you another example of a solution for a typical task. Somehow, it often causes difficulties for many people.
Here is given a table of payments (id INT, uid INT, pay_date DATETIME, amount DECIMAL(15, 2)).

id – the primary key
uid – the user id
pay_date – the payment date
amount – the amount of payment

You need to run a query that would display the date and amount of the last payment for each user.

UPD. Let’s suppose that the user cannot make more than one payment per second (a task statement without this condition is incorrect). Type of pay_date is changed from DATE to DATETIME.
I offer you the following standard solution:

SELECT p.uid, p.amount
FROM payments p
(SELECT uid, MAX(pay_date) AS max_dt
FROM payments
GROUP BY uid) sel ON p.uid = sel.uid AND p.pay_date = sel.max_dt;
ZimerMan 26 may 2014, 15:03
Vote for this post
Bring it to the Main Page


Leave a Reply

Avaible tags
  • <b>...</b>highlighting important text on the page in bold
  • <i>..</i>highlighting important text on the page in italic
  • <u>...</u>allocated with tag <u> text shownas underlined
  • <s>...</s>allocated with tag <s> text shown as strikethrough
  • <sup>...</sup>, <sub>...</sub>text in the tag <sup> appears as a superscript, <sub> - subscript
  • <blockquote>...</blockquote>For  highlight citation, use the tag <blockquote>
  • <code lang="lang">...</code>highlighting the program code (supported by bash, cpp, cs, css, xml, html, java, javascript, lisp, lua, php, perl, python, ruby, sql, scala, text)
  • <a href="http://...">...</a>link, specify the desired Internet address in the href attribute
  • <img src="http://..." alt="text" />specify the full path of image in the src attribute