DPC18 - Making the most out of MySQL

MySQL 5.7 is two years old and adoption is growing. The new JSON Data Type shines away as the most talked feature in this version. But they are by no means the only awesome things this it has to offer. Learn how to manipulate JSON fields and how Generated Columns can help you index data and much more with real life examples.

Transcript

1. Gabriela D'Avila Ferrara @gabidavila gabriela.io/feedback | https://joind.in/talk/8884c Making the most out of MySQL 1
2. @gabidavila •Developer Advocate •Lego Hoarder •@gabidavila •http://gabriela.io 2 $ whoami •Ada's mom: @ada_doglace
3. @gabidavila 3 !
4. @gabidavila What to expect? •DDL Changes •SQL Modes •Generated Columns •JSON Data Type 4
5. @gabidavila DDL changes on InnoDB 5
6. @gabidavila Online DDL Changes •In place (ALGORITHM=INPLACE) • Rename index • VARCHAR from 1B to 255B • Add a Virtual Column 6
7. @gabidavila Online DDL Changes 7 ALTER TABLE addresses CHANGE COLUMN state state VARCHAR(255); ALTER TABLE addresses RENAME INDEX `state_index` TO `ix_state`;
8. @gabidavila •Table-copy (ALGORITHM=COPY) • VARCHAR from 256B to 65535B • Type conversion • Add a column* (<=5.7) Normal DDL changes 8
9. @gabidavila ALTER TABLE with COPY •1st step: create new table structure with newer definition •2nd step: copy the data from the original table to the new table •3rd step: drop the old table and rename the new table 9
10. @gabidavila Normal DDL Change 10 ALTER TABLE addresses CHANGE COLUMN user_id user_id BIGINT; ALTER TABLE users ADD COLUMN password_digest VARCHAR(255);
11. @gabidavila SQL Modes 11
12. @gabidavila SQL Modes (SELECT @@GLOBAL.sql_mode;) 12 On MySQL 5.6: +--------------------------------------------+ | @@GLOBAL.sql_mode | +--------------------------------------------+ | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+ 1 row in set (0.00 sec) On MySQL 5.7 and 8.0: +-----------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +-----------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE, |
 | ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------+ 1 row in set (0.00 sec)
13. @gabidavila MySQL 5.6: •STRICT_TRANS_TABLES •NO_ENGINE_SUBSTITUTION MySQL 5.7 & 8.0: •STRICT_TRANS_TABLES •NO_ENGINE_SUBSTITUTION •ONLY_FULL_GROUP_BY •NO_ZERO_IN_DATE* •NO_ZERO_DATE* •ERROR_FOR_DIVISION_BY_ZERO* SQL Modes comparison 13 Full list of sql_mode
14. @gabidavila ERROR_FOR_DIVISION_BY_ZERO •Write Operation: UPDATE mysql> UPDATE users SET id = id/0 WHERE id = 2; ERROR 1365 (22012): Division by 0 14 •Read Operation: SELECT mysql> SELECT id, first_name, last_name, id/0 FROM users WHERE id = 2; +----+------------+-----------+------+ | id | first_name | last_name | id/0 | +----+------------+-----------+------+ | 2 | Gabriela | Ferrara | NULL | +----+------------+-----------+------+ 1 row in set, 1 warning (0.00 sec)
15. @gabidavila 15
16. @gabidavila ONLY_FULL_GROUP_BY 16
17. @gabidavila Table `users` 17 Field Type Null Key Default Extra id int(10) unsigned NO PRI auto_increment first_name varchar(255) NO NULL last_name varchar(255) NO NULL email varchar(255) NO NULL twitter_info json YES
18. @gabidavila Repeated emails 5.6 18 5.6 SELECT * FROM users GROUP BY email HAVING count(*) > 1 +-------+------------+-----------+---------------------+--------------------------------+ | id | first_name | last_name | email | twitter_info | +-------+------------+-----------+---------------------+--------------------------------+ | 8965 | Conor | Quitzon | yjacobson@yahoo.com | {"id": 100838242, "url": "http | | 69772 | Junius | Mante | nkuhlman@gmail.com | {"id": 20039476, "url": "https | | 66525 | Katelynn | Feil | qgottlieb@gmail.com | {"id": 111644778, "url": "http | | 92577 | Tillman | Nienow | zzieme@yahoo.com | {"id": 1359073920, "url": "htt | | 18046 | Guillermo | Lebsack | cdoyle@gmail.com | {"id": 120064855, "url": null, | +-------+------------+-----------+---------------------+--------------------------------+ 5 rows in set (0.65 sec)
19. @gabidavila Repeated emails 19 SELECT * FROM users WHERE email = 'cdoyle@gmail.com' +-------+------------+-----------+------------------+--------------+ | id | first_name | last_name | email | twitter_info | +-------+------------+-----------+------------------+--------------+ | 18046 | Guillermo | Lebsack | cdoyle@gmail.com | {...} | | 20559 | Marietta | Quitzon | cdoyle@gmail.com | {...} | +-------+------------+-----------+------------------+--------------+ 2 rows in set (0.04 sec)
20. @gabidavila Repeated emails 5.6 & 8.0 20 5.7 8.0 mysql> SELECT * FROM `users` GROUP BY `email` HAVING count(*) > 1; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘social.users.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT * FROM users GROUP BY email HAVING count(*) > 1
21. @gabidavila Why? "Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns." 21
22. @gabidavila Hm... 22
23. @gabidavila MySQL to human Translation This happens because the query is grouping by email and MySQL is magically trying to “guess” which field it should bring as a result to the other columns (id, first_name, etc.) and it gives an error since there are more than one result per row and no aggregator was used to specify the correct value. 23 More Info
24. @gabidavila The Fix 🛠 24
25. @gabidavila Aggregators!!! •min(column) •max(column) •group_concat(column) •count(column) or count(*) •sum(column) 25
26. @gabidavila Old way: using the magic 🎩 26 SELECT * FROM users GROUP BY email HAVING count(*) > 1 <?php // Doctrine $qb = $entityManager->createQueryBuilder(); $repeatedUsers = $qb->select('*') ->from('users', 'u') ->groupBy('u.email') ->having('count(*) > 1'); // Eloquent $repeatedUsers = Capsule::table('users') ->select('*') ->groupBy('email') ->havingRaw('count(*) > 1');
27. @gabidavila What is the "right" way? 🤔 27
28. @gabidavila 1st step: say what you need 28 SELECT id, first_name, last_name, email, twitter_info FROM users GROUP BY email HAVING COUNT(*) > 1; 5.6 ✅ Works 5.7 ⛔ Doesn’t Work 8.0 ⛔ Doesn’t Work
29. @gabidavila 2nd step: adding aggregators 29 SELECT group_concat(id) AS all_users_id, first_name, last_name, email, twitter_info FROM users GROUP BY email HAVING COUNT(*) > 1; 5.6 ✅ Works 5.7 ⛔ Doesn’t Work 8.0 ⛔ Doesn’t Work
30. @gabidavila 2nd step: some more column aggregators... 30 SELECT group_concat(id) AS all_users_id, min(first_name) AS first_name, last_name, email, twitter_info FROM users GROUP BY email HAVING COUNT(*) > 1; 5.6 ✅ Works 5.7 ⛔ Doesn’t Work 8.0 ⛔ Doesn’t Work
31. @gabidavila 3rd step: almost everywhere 31 SELECT group_concat(id) AS all_user_ids, min(first_name) AS first_name, min(last_name) AS last_name, email, any_value(twitter_info) AS twitter_info FROM users GROUP BY email HAVING COUNT(*) > 1; 5.6 ✅ Works 5.7 ✅ Works 8.0 ✅ Works You can change the min(column) aggregator to any_value(column) if the row value for that column doesn't matter
32. @gabidavila DEMO 32
33. @gabidavila ONLY_FULL_GROUP_BY trends 33
34. @gabidavila How do I disable it? 34 You don't.
35. @gabidavila 35
36. @gabidavila Generated Columns 36
37. @gabidavila Virtual •No disk space •In-place change operation •Value generated on demand and on every BEFORE triggers •Faster creation/ slower read Stored •Uses disk space •Copy operation •Updated on every INSERT and UPDATE •Slow creation/ faster read Types of Generated Columns 37
38. @gabidavila •They only know the table domain •They have a type •Allows expressions to be used such as: • Operators (product * quantity) • Built-in functions (YEAR(dob)) • Literals ("expression", 1) •Can be indexed •Subqueries ARE NOT allowed •Custom functions ARE NOT supported •Non-deterministic expressions ARE NOT supported Virtual & Stored: common features 38
39. @gabidavila DEMO 39
40. @gabidavila Division by Zero 40 Virtual • Legal • Returns NULL Stored • Illegal • Returns a "Division by 0" error with these side effects: • Not inserting/updating the row • Not creating the column at all if made through an ALTER TABLE Bug #88901
41. @gabidavila Dependency •Generated Columns can depend on other generated columns •Ordering of Generated Columns matter when using another generated column •It can reference a non-generated column no matter the definition order 41
42. @gabidavila DEMO 42
43. @gabidavila JSON 43
44. @gabidavila JSON •Stored as Binary, not TEXT •TEXT fields can be converted •Accessible by column->"$.value" (JSON_EXTRACT alias) •Indexing is possible only through Generated Columns (Virtual or Stored) 44
45. @gabidavila Converting TEXT to JSON •It is costly, uses table COPY operation •All rows must be valid, else an error occurs • Use JSON_VALID() before 45
46. @gabidavila DEMO 46
47. @gabidavila Other features 47
48. @gabidavila Features & deprecations •Passwords can now have an expiration date •NO_ZERO_DATE, NO_ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO are deprecated and being default in STRICT mode in future versions •Tables now supports more than one trigger per event •YEAR(2) was deprecated on 5.6 and completely removed on 5.7 •GRANT statement for user creation is deprecated on 5.7 and removed on 8.0 48
49. @gabidavila MySQL 8.0 bits 49
50. @gabidavila MySQL 8.0 bits •New default encoding •Data Dictionary •Users now can have ROLES •Instantaneously add columns (ALGORITHM=INSTANT) •CATS instead of FIFO for high concurrency •JSON partial update •Window Functions •Common Table Expressions •Better Geospatial support •Use MySQL without SQL (through MySQL Shell) 50
51. @gabidavila Thank you! '. •Feedback & Slides: gabriela.io/feedback •Twitter: @gabidavila •Don't be shy! Come on and talk to me! 51 QUESTIONS