CODEWITHSUNDEEP
mysqlmysql-workbench
Kaique Andrade
Kaique Andrade

Reputation: 3

MYSQL strict mode, how to use it?

I've come to know about this strict mode while on my course, i was advised to turn it off i and for the moment i did. But if strict mode is there, it surely has a reason for it. First let me show the code that got the error

SELECT `first_name`,
       `last_name`, 
       `order_date`, 
       `amount` 
 FROM `customers`
    JOIN `orders`
        ON `customers`.`id` = `orders`.`customer_id`
GROUP BY `orders`.`customer_id`;

and i got ERROR CODE 1055

error image

for what i was told, i had to use all my fields in the GROUP BY unless aggregated.

So it should be like this.

SELECT `first_name`,
       `last_name`, 
       `order_date`, 
       `amount` 
 FROM `customers`
    JOIN `orders`
        ON `customers`.`id` = `orders`.`customer_id`
GROUP BY `orders`.`customer_id`,`order_date`, 'amount';

The code would "work" but it would group by order_date and 'amount', with i didn't want to happen.

for the amount i had to use a SUM(), so i didn't had to use it anymore on GROUP BY, but order_date is still there.

Since amount is still here it would show all the date options, i do know that it is possible to have only one date per id=id

for future reference, how should i use in good ways the STRICT MODE, or should i turn it off?

Upvotes: 0

Views: 1501

Answers (1)

Mikhail Burshteyn
Mikhail Burshteyn

Reputation: 5012

  1. Generally strict mode is preferred in MySQL as it helps you avoid some common mistakes such as this one.

  2. When you select columns that are not included in your GROUP BY clause without any aggregations, MySQL without strict mode may return any random row, and you cannot make any assumptions about which row would be returned. MySQL in strict mode, as many other RDBMS, raises an error so that you are forced to fix your query.

  3. To write your query correctly, you first need to figure out what data do you want to get for order_date and amount -- e.g. should it be the newest order made by customer or something else.

Upvotes: 1

Related Questions