Some interesting tricks and features of working with MySQL

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

Remove duplicates

Very often at various specialized resources I met questions about how to get rid of duplicate records in the table faster and more optimally. Immediately it comes to mind that you need to create another table that is identical to this one, create a unique key in it and copy the data from the source table into it, for example, using INSERT IGNORE. But there is a simpler way. Simply create a unique key in the table using the following query:

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

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

Convert string to number

Suppose you are faced with the 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 text field num, containing values ​​of the type '1', '1a', '1 / b', '2y', '3utsuken', etc. And we want to choose houses with a number consisting of 1 and some other characters. I think many will immediately rush to find a solution using LIKE or REGEXP. But it will be easier to use the following MySQL feature:
')

 SELECT * FROM address WHERE num + 0 = 1; 

Upon encountering an arithmetic operation, MySQL automatically leads all arguments to a numeric type. In the case of strings, all characters will be simply truncated, starting with the first non-numeric character.
Such a request is also 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 life easier for us.
We have the following table1 table:

id	sum
one	35
2	25
3	ten
four	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 entry, for the first entry, onStart = 0.
Those. in the end, we should get this result:

id	sum	onStart	total
one	35	0	35
2	25	35	-ten
3	ten	-ten	20
four	55	20	35
five	12	35	-23

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

 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 entries in the table

Another common problem. And here I will also give an example immediately.
Given table1 (id, f1, f2). We need to write a request that would return the following result:

total records	number of records with f1 = 1	the sum 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 it is obvious that this is not the optimal solution. It is necessary to perform 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 such a request:

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

It can be seen that in the GROUP BY block we forgot to specify the alias of the id field, and accordingly, when trying to execute the query, we received the error "Column 'id' in group statement is ambiguous". It would seem all right. Now we change this query:

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

We removed from the list of output fields t2.id and, about a miracle, the request worked, the data were grouped by t1.id. Other DBMS, such as, for example, MS SQL or PostgreSQL, and in the second case would give an error, but for MySQL the second query is completely correct.
So I recommend that you be more attentive and always use aliases before the fields, otherwise then with a small change in the request, you can run into an error.

Last-date data search

And finally, I want to give another example of solving one typical, not complicated, frequently encountered problem. For some reason, it often causes difficulties for many.
Given the payment table payments (id INT, uid INT, pay_date DATETIME, amount DECIMAL (15, 2)).
id - primary key
uid - user ID
pay_date - date of payment
amount - amount of payment
You need to write a request that would display the date and amount of the last payment for each user.
UPD. We believe that the user can not spend 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;