Some interesting tricks and features of working with MySQL

I think that in the process of studying a particular DBMS, each of you more than once invented bicycles to solve your problems, not knowing about the existence of a particular function or technique that could speed up query execution at times and reduce the amount of code. In this article I want to share with you my experience with very “good” and “responsive” MySQL, which often allows the programmer to do things that other DBMSs could not digest. The material will be useful rather for those who just decided to delve into the wonderful world of queries, but perhaps experienced programmers will find something interesting here.

Duplicate Removal

Very often, at various specialized resources, I came across questions about how to quickly and optimally get rid of duplicate records in a table. Immediately it comes to my mind that you need to create another table identical to this one, create a unique key in it and copy the data from the original table into it, say, using INSERT IGNORE. But there is an easier way. Simply create a unique key in the table using this query:

ALTER IGNORE TABLE table1 ADD UNIQUE (field1, field2);

After adding the key, all duplicates will be deleted automatically.

Convert string to number

Let's say you have a task of searching in the address table of the address of a house with a certain number. Moreover, the house number is stored in the num text field containing values ​​of the type '1', '1a', '1 / b', '2y', '3ytsuken', etc. And we want to choose a house with a number consisting of 1 and some other characters. I think many will immediately rush to look for a solution using LIKE or REGEXP. But it will be easier to use the following MySQL feature:

SELECT *
  FROM address
  WHERE num + 0 = 1;

Having met an arithmetic operation, MySQL automatically casts all arguments to a numeric type. In the case of strings, all characters will be simply truncated, starting with the first non-numeric one.
Here is such a request, too, quietly executed without errors:

SELECT '1qwe3s' + '2regt3g';

And as a result, we get the answer: 3.

Using Variables in Queries

Here I will immediately give an example of solving a problem in which a variable will make our life easier.
We have the following table table1:
id sum
1 35
2 25
3 ten
4 55
five 12

You need to display all these fields and add 2 more to them, onStart and total.
total = summ - onStart.
onStart is equal to the total value from the previous record, for the first record onStart = 0.
That is, in the end, we should get this result:
id sum onStart total
1 35 0 35
2 25 35 -ten
3 ten -ten 20
4 55 20 35
five 12 35 -23

Using a variable, we will be able to get rid of extra JOINs and subqueries when solving this problem:

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

Counting the number of different records in a table

Another common task. And here I will immediately give an example.
Given table table1 (id, f1, f2). We need to write a query that would return the following result to us:
total number of records number of records with f1 = 1 the sum of the values ​​of f2 for f1 = 2

Of course, you can get the result like this:

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

But obviously, this is far from the optimal solution. There are two additional subqueries for each record. And we will do it differently:

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

Now another thing. All that we need, we counted in one pass on the table.

Column 'id' in group statement is ambiguous

In this part of the article, I want to draw your attention to one interesting feature of MySQL.
We have the following request:

SELECT t1.id, t2.id
  FROM table1 t1
  JOIN table2 t2 ON t1.id = t2.id_t1
  GROUP BY id;

It is seen that in the GROUP BY block we forgot to specify the alias for the id field, and accordingly, when trying to execute the request, we received the error "Column 'id' in group statement is ambiguous". It would seem that everything is true. Now modify this query:

SELECT t1.id, t2.f1
  FROM table1 t1
  JOIN table2 t2 ON t1.id = t2.id_t1
  GROUP BY id;

We removed t2.id from the list of displayed fields and, lo and behold, the request worked out, the data was grouped by t1.id. Other DBMSs, such as, for example, MS SQL or PostgreSQL, would give an error in the second case, but for MySQL the second query is completely correct.
So I recommend that you be more careful and always use aliases in front of the fields, otherwise then with a slight change in the request, you may run into an error.

Search for last date data

And finally, I want to give another example of solving one typical, not complicated, often encountered problem. For some reason, it often causes difficulties for many.
The payments table is given payments (id INT, uid INT, pay_date DATETIME, amount DECIMAL (15, 2)).
id - primary key
uid - user identifier
pay_date - payment date
amount - payment amount
You need to write a request that displays the
date and amount of the last payment for each user.
UPD. We believe that the user cannot make more than one payment per second. (Without this condition, the statement of the problem is incorrect). Pay_date type changed from DATE to DATETIME.
I offer you the following standard solution:

SELECT p.uid, p.amount
  FROM payments p
  JOIN
    (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;