Northeast PHP

August 4, 2017

Next week Northeast PHP returns to Price Edward Island and Charltottetown. Last year this event moved from Cambridge to Canada and found a wonderful audience. This year the conference is back and bigger than ever. And the local government is extremely supportive and had provided the organizers with a grant to help this year.

I will be presenting on Basic PHP Database Programming and Getting JSON Data in and Out of MySQL. Tickets are still available and there is a student discount.

Advertisements

MySQL 5.7.13 Hidden Gems

June 28, 2016

The Release Notes for MySQL 5.7.13 has some interesting notices about account length, JSON short cuts, and more.

A Rose By Any Other Name

Way back in 5.7.8 the account username was increased from sixteen to thirty two characters. Yeah! For many years this has been heavily requested. Now this has been incorporated in the definer fields of event and proc tables plus the grantor field of procs_priv and tables_priv tables. To do this these columns where bumped up from CHAR(77)) to CHAR(93) to hold the longer username@hostname data.

JSON Inline Path

An unquoting extraction operator ->>, called an inline path operator, is for use with JSON documents stored in MySQL. The new operator is similar to the -> operator which is a shotcut for JSON_EXTRACT. But it goes a step further in that it performs JSON unquoting of the value as well. For a JSON column mycol and JSON path expression mypath, the following three expressions are equivalent:


JSON_UNQUOTE( JSON_EXTRACT(jsoncolumn, "$.jsondata") )
JSON_UNQUOTE(jsoncol->"$.jsondata")
mycol->>"$.jsondata"

This new ->> operator can be used in SQL statements where JSON_UNQUOTE(JSON_EXTRACT()) would be allowed. This includes SELECT lists, WHERE and HAVING clauses, and ORDER BY and GROUP BY clauses.

Systemd and Multiple Severs

Those of you running Systemd equipped distros need to read Configuring Multiple MySQL Instances Using systemd if you desire to run multiple servers on a single host.

And there is more

The release notes for ’13 cover bugs fixed, audit log granularity, and more. Please take a few minutes to read this.


San Diegans — learn about MySQL’s New JSON Data Type Tonight

January 19, 2016

I will be speaking at the San Diego PHP Meetup tonight January 19th at 7pm. The location is Business.com Media, 1900 Wright Place in Carlsbad (33.122360, -117.28909) and the topic will be MySQL’s JSON Data Type. I hope to see you there!


JSON and MySQL 5.7

October 8, 2015

In the past few months I have been inundated with questions about the new JSON data type. The man page for JSON is fascinating reading. So grab your favorite beverage, the JSON manual page, and take a comfy seat for reading. There are many highlights but be sure to check out some of these highlights.

  • Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error and this is a big advantage over putting JSON data in a string column.
  • JSON columns can not have a DEFAULT value, can not be indexed (virtual columns for secondary indexes are okay)
  • MySQL handles strings used in a JSON columnusing the utf8mb4 character set and utf8mb4_bin collation.

One thing to note is that strings are normalized. If you duplicate a key the new value is discarded.
SELECT JSON_OBJECT('first', 1, 'second', 2, 'first', 3, 'third', 4);
Will return
'{\"first\": 1, \"third\": 4, \"second\": 2}'

The second ‘first’ goes away. So not only does your JSON need to be well formed but you can not reuse keys in a sting.

If you use JSON_MERGE the values for that key are merged into an array.
SELECT JSON_MERGE('{"first": 1}','{"first": 2}')
Here First-1 and First-2 are merged into a single array.
'{\"first\": [1, 2]}'

A little more complex example for illustration.
SELECT JSON_MERGE('{"first": 1}','{"second": 3}', '{"first": 2}')
will produce
'{\"first\": [1, 2], \"second\": 3}'

And how big ban a JSON doc be? IT can not be stored longer than the max_allowed_packet system variable setting but it can be longer in memory for manipulation. And the protocol limit for max_allowed_packet is one gigabyte. So docs longer than 1GB will need to be saved in 1GB pieces.


Indexing MySQL JSON Data

June 9, 2015

“MySQL’s JSON data type is great! But how do you index the JSON data?” I was recently presenting at the CakePHP Cakefest Conference and was asked that very question. And I had to admit I had not been able to play, er, experiment with the JSON datatype to that level. Now I have and it is fairly easy.

1. Create a simple table
mysql> desc colors;
+--------------+----------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------------------+
| popular_name | char(10) | YES | | NULL | |
| hue | json | YES | | NULL | |
+--------------+----------+------+-----+---------+-------------------+
2 rows in set (0.00 sec)

2. Add in some data
INSERT INTO `colors` VALUES ('red','{\"value\": \"f00\"}'),('green','{\"value\": \"0f0\"}'),('blue','{\"value\": \"00f\"}'),('cyan','{\"value\": \"0ff\"}'),('magenta','{\"value\": \"f0f\"}'),('yellow','{\"value\": \"ff0\"}'),('black','{\"value\": \"000\"}');

3. SELECT some data
Use the jsb_extract function to efficiently search for the row desired.
mysql> select jsn_extract(hue, '$.value') from colors where jsn_extract(hue, '$.value')="f0f";
+-----------------------------+
| jsn_extract(hue, '$.value') |
+-----------------------------+
| "f0f" |
+-----------------------------+
1 row in set (0.00 sec)

But how efficient is that? Turns out we end up doing a full table scan.

mysql> explain select jsn_extract(hue, '$.value') from colors where jsn_extract(hue, '$.value')="f0f";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | colors | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

4 Add a VIRTUAL column to index quickly
mysql> ALTER TABLE colors ADD value_ext char(10) GENERATED ALWAYS AS (jsn_extract(hue, '$.value')) VIRTUAL;
This will add a virtual column from the value data in the hue column.

5 Index the New Column
mysql> CRATE INDEX value_ext_index ON colors(value_ext);

Now the EXPLAIN shows us that we are more efficient.
mysql> explain select jsn_extract(hue, '$.value') from colors where value_ext="f0f";
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | colors | NULL | ref | value_ext_index | value_ext_index | 11 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>