Midwest PHP 2018 Header

Gabriela went over several of the new features in MySQL 5.7 and discussed the fact that 5.7 has a more strict set of settings to make sure you get the correct results.

Main Take Aways

  • 5.7 has an inplace schema change option
  • 5.7 has JSON support
  • 5.7 has a sys schema that gives you information about slow queries and unused indexes

Things I’m Going to Do

Try upgrading all my application to 5.7. I tried one without adjusting the sql_mode and it cause a hugh failure rate on my integration tests.

My Raw Notes

  • MySQL keeps getting more strict
  • 5.7 appears to be overtaking 5.5

Online DDL changes

  • Inplace or copy
  • Inplace quicker
    • use ALGORITHM=INPLACE
    • add index
    • add virtual column
    • varchar 1 to 255
  • Copy slower
    • Droping column
    • varchar 256+

JSON

  • Can store JSON
  • Use JSON_EXTRACT to get single field
  • select JSON_EXTRACT(field, ‘$.nickname’) or select field->’$.nickname’
    • will return quoted strings and null if value is missing
  • select field-»’$.nickname’
    • removes quotes from strings for easier reporting
    • this method easier to read
  • $ refers to document itself

Generated Columns

  • Virtual
    • No disk space
    • In-place change
    • Value generated on demand and on every BEFORE trigger
  • Stored
    • Uses disk space
    • Copy operation
    • Updated on every INSERT and UPDATE
  • Both
    • Only know about their own table
    • Must have a type
    • Allows expressions
      • operators (math)
      • Built-in functions
      • Literals
    • Can be indexed
  • Limitations
    • Subquries not allowed
    • Custom functions not supported
    • Can’t rename the column
    • Can’t use non-deterministric functions (IE now())*

note: When rows affected show up in alter table results it caused a rebuild*

Generated Columns and JSON indexing

  • Can’t index json field directly
  • Need to create generated column and then index that column*

sql_mode

  • Determines how strict database is going to be
  • In 5.7 MySQL when from 2 options to a bunch
  • Don’t disable any fix your problems

sys schema

  • Don’t enable in production by default
  • Used for critical analytical cases
  • Allows you to create dashboards to show usage
    • Show high cost SQL statements
    • Top 5% of slow queries
    • Show Unused index
    • show full table scans

Other changes

  • Passwords can have expiration dates
  • Triggers now support more than one event per table