MySQL – Nikolay Nikolov blog

Get all duplicate records in table

Slow variant:

SELECT *
FROM table t1
WHERE t1.domain_id = 1569
AND t1.is_active = 1
AND (t1.field IN (SELECT t2.field
				 FROM table t2
				 WHERE t2.is_active = 1
				 GROUP BY t2.field
				 HAVING COUNT(t2.field) > 1))

Faster variant:

SELECT l0_.*
FROM table l0_
inner join table n2 on n2.field = l0_.field
where l0_.id <> n2.id
AND l0_.is_active = 1
Group By l0_.id

QueryBuilder:

$queryBuilder
	->innerJoin(Table::class,'table', Query\Expr\Join::WITH, 'entity.field = table.field')
	->andWhere('entity.id <> table.id')
	->andWhere('table.isActive = 1')
	->groupBy('entity.id')
;

Problem with NULL values you should remember

When trying to execute SELECT OR UPDATE query for rows different than a specific value, we should be aware to insert additional statement for null values too.

For Example:

        UPDATE account_event 
        SET account_event.assigned_to_id = 2 
        WHERE account_event.status != 1

With this query your goal is to update all rows where account_event.status is different from 1. But if status have null value it wont be updated!
In MySQL all null elements are skipped from comparison and will never be Selected or Updated.

When you have a null fields you should always add additional statement for null like this:

        UPDATE account_event 
        SET account_event.assigned_to_id = 2 
        WHERE account_event.status != 1 
        OR account_event.status IS NULL

Lifehack – Delete rows from table with foreign keys

First deactivate foreign key check. Next execute the delete statement. Don’t forget to activate foreign key check again.

Example:

SET FOREIGN_KEY_CHECKS=0;

DELETE FROM user
WHERE id IN (55,62);

SET FOREIGN_KEY_CHECKS=1;

MySQL Foreign Keys

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

* If you want to use foreign key all your tables should be with InnoDB engine.

Foreign Key on create table

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

Continue reading “MySQL Foreign Keys”

Query to search in JSON column

The promlem is to search in json column. Using MYSQL REGEXP is the solution:

SELECT * 
FROM  `orders` 
WHERE  `json` REGEXP  '(.*"op-number-29":"".*)'

If we want to get all rows where op-number-29 is not equel to “”:

SELECT * 
FROM  `orders` 
WHERE  `json` NOT REGEXP  '(.*"op-number-29":"".*)'

Generating new id’s in ascending order in MySQL

Simple example of generating new id’s in ascending order in MySQL.

SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;

How to update multiple rows with one query

It’s not so hard to update multiple rows with only one query. All we need to do is to use MySQL “CASE” functionality.

mysql_query(" update category
    set price = 
    case 
    when name = 1 then '$_POST[price1]'
    when name = 2 then '$_POST[price2]'
    when name = 3 then '$_POST[price3]'
    end
    WHERE name in (1,2,3)");