Output MySQL in JSON Format

August 13, 2018

I was recently asked if there is anyway to select non-JSON data from a MySQL database and output that data in JSON format. There are two very easy ways to to this.

JSON Array

If you need a JSON array, it is very easy to use the JSON_ARRAY() function. Simply select the columns you want as the argument to JSON_ARRAY()

SQL> select
json_array(continent,Population, Code)
from country
limit 5;
| json_array(continent,Population, Code) |
| ["North America", 103000, "ABW"] |
| ["Asia", 22720000, "AFG"] |
| ["Africa", 12878000, "AGO"] |
| ["North America", 8000, "AIA"] |
| ["Europe", 3401200, "ALB"] |
5 rows in set (0.0006 sec)


However to get a JSON object you need to do a little more work. JSON objects require pairs – key/value pairs — so you need to pass a key, even a fictitious one for each value you desire. In the example below I use strings with the name of the column to pair up with the values

json_object('Continent', continent,
'Population', Population,
'Code', Code)
from country
limit 5;
| json_object('Continent', continent, 'Population', Population, 'Code', Code) |
| {"Code": "ABW", "Continent": "North America", "Population": 103000} |
| {"Code": "AFG", "Continent": "Asia", "Population": 22720000} |
| {"Code": "AGO", "Continent": "Africa", "Population": 12878000} |
| {"Code": "AIA", "Continent": "North America", "Population": 8000} |
| {"Code": "ALB", "Continent": "Europe", "Population": 3401200} |
5 rows in set (0.0007 sec)

Remember if you have questions about this or other MySQL JSON data type programming, please refer to my book MySQL and JSON A Practical Programming Guide -- David Stokes

MySQL Group Replication July 7th at DFW Unix User Group

June 20, 2018

I will be speaking at the Dallas Fort Worth Unix User Group meeting July 7th MySQL Group Replication. Some folks asked for a reminder well beforehand especially with the upcoming US Holiday weekend, so here it is.

The meeting location is the IBM Innovation Center at 1177 South Beltline Road, in Coppell, just south of the Airline Drive traffic light. And just west of North Lake. Pizza at 6:45 and me at 7:00PM.
MySQL Group Replication Topology Image

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.

MySQL 8 and Common Table Expressions – An Introduction

July 31, 2017

MySQL 8 will have Common Table Expressions also known as CTEs.  I have had people asking me when  MySQL would have CTEs for years and I am glad to report they are ready for testing.

What Does a CTE look like?

The keyword WITH is the big hint that the statement is a CTE.  For now think of a CTE as a collections of views or subqueries that you combine together for the desired results. After the WITH clause, one or more comma separated sub-clauses that are subqueries. And CTEs can reference other CTEs.  For a first example, let us combine the world_x example database’s city and country table to produce a list of all cities and their corresponding countries.

  cte1 as (select Name as CName, CountryCode as CCode from city),

  cte2 as (select Name as Name, Code from country)
SELECT cte1.CName, cte2.Name from cte1
  join cte2 on (cte1.CCode = cte2.Code)
  order by cte1.CName;

So cte1 gets all the city tables Name  and CountryCode data and cte2 grabs the country  Name and Code columns.  The CTE part of this query is in blue. The city CountryCode is a corresponding key to the country Code column.  The part of the query in green is how the CTE is queried. You could consider it a short lived temp table. And here is the abbreviated output.

| CName               | Name              |
| A Coruña (La Coruña) | Spain |
| Aachen              | Germany        |
| Aalborg             | Denmark        |
| Aba                    | Nigeria            |

Got a Simpler Example?

mysql>WITH cte as (SELECT ID, Name, District FROM city)
select Name, ID from cte limit 5;

| Name      | ID |
| Kabul      | 1 |
| Qandahar | 2 |
| Herat       | 3 |
| Mazar-e-Sharif | 4 |
| Amsterdam | 5 |
5 rows in set (0.00 sec)

This second example has a CTE named cte (and the Nobel Prize for hyper creating naming goes to …). From this CTE we can then select what information we desire from it. Sp in this way it is kinda of like a View. In the above example, the CTE grabs three columns of data but from it we only take two columns. And you can name you CTE what every you want within the normal constraints.

Plus Recursive!

And CTEs can also be recursive. A recursive common table expression is a CTE with a subquery that refers to its own name. At the beginning it will proclaim WITH RECURSIVE. You will often find them used with hierarchical data such as organization charts and assembly build sheets. The following example counts down from 10

mysql> WITH RECURSIVE rcte (x) AS
SELECT x – 1 FROM rcte WHERE x > 1)
| x |
| 10 |
| 9 |
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
10 rows in set (0.00 sec)

Where to use CTEs

CTEs are often easier to read than embedded subqueries. They disappear after the query is executed which makes them easier to manage than using temp tables.  And many think they are easier to understand than subqueries which hopefully means easier maintenance.


So lets run EXPLAIN on the first example, minus the ORDER BY:

mysql> EXPLAIN with cte1 as (select Name as CName, CountryCode as CCode from city), cte2 as (select Name as Name, Code from country) select cte1.CName, cte2.Name from cte1 join cte2 on (cte1.CCode = cte2.Code);
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4079 | 100.00 | NULL |
| 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 9 | world_x.city.CountryCode | 1 | 100.00 | NULL |
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\g
| Level | Code | Message |
| Note | 1003 | /* select#1 */ select `world_x`.`city`.`Name` AS `CName`,`world_x`.`country`.`Name` AS `Name` from `world_x`.`city` join `world_x`.`country` where (`world_x`.`country`.`Code` = `world_x`.`city`.`CountryCode`) |
1 row in set (0.00 sec)

The show warnings lets us peek at the actual query the MySQL optimizer will run. The optimizer was able to rewrite the query as a fairly simple join. So in this case at least, the performance will be fine.



Mysqlsh on Windows

April 20, 2016

Been seeing references to the new document store features of MySQL 5.7 and want to try it on Windows? The new features of MySQL require a download of the new mysqlsh msi or zip.


Welcome to MySQL Shell

Installation is a snap.

You can also determine where to keep your new mysql

You can also determine where to keep your new mysql

The installation msi quickly guides you through the choices and your best bet for now is to pick the defaults. If you have used the 5.7 msi server install before there are no surprises. But you are offered the opportunity to change the options for such things such as location of the binaries. Again the defaults worked well for me. In a very short time you can launch the new shell.

And then you are ready to use mysqlsh.

new shell

And then you have the new shell with the new protocol!

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.

Webinar: The Proper Care and Feeding Of MySQL Databases for Busy Linux Administrators with David Stokes

September 15, 2015

Wednesday, September 16, 2015 12 PM EST / 4 PM GMT

Are you a Linux Admin who ‘also has’ MySQL DBA responsibilities but no DBA training? Do you wonder why databases are the problem children of all the software services you are responsible for? Then this webinar is for you. You’ll learn all about:

  • Why databases do not play well with others
  • How (and how often) you should be doing backups
  • How MySQL use authentication is ‘too permissive’
  • Which configuration hints keep the server happy
  • Other best practices to keep your MySQL instances happy and shiny for years to come

Join us for this exciting live webinar on what you should know about the proper care and feeding of MySQL databases, with Dave Stokes, MySQL Community Manager at Oracle, and Janis Griffin, Database Performance Evangelist at SolarWinds. Register here!

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)


Color Your MySQL Console

January 13, 2015

I started programming in the era of punch cards and learned to love monochrome screens quickly. The standard MySQL client console is a echo of those days. But we can get color.

One of the really interesting parts of my job is looking at the new submissions to Planet.MySQL.Com and seeing what interesting things people are doing with MySQL. Today I found Nicola Strappzaaon’s Swapbytes.com in the approval queue and his top article at the time was Give a little color to the MySQL Console. Actually the tile read Darle un poco de color a la consola de MySQL but Google translate overcame my insufficient skills.

Basically you install grcat ‘universal coulouriser’, install a configuration file, and tweak the .my.cnf file to use grcat as the pager. Nicola’s blog goes into more detail but here is the gist.

apt-get install grc
https://raw.githubusercontent.com/nicola51980/myterm/master/bash/dotfiles/grcat wget -O ~ / .grcat

And the .my.cnf file

pager = grcat ~/.grcat | less -RSFXin

Colorized MySQL Console

Color Added MySQL Console is quick and easy to do do.

I had change the .grcat configuration file as there was a complaint about a bad color ‘whit’ but replacing the first ‘white’ in the file with red solved that quickly. So change white to red under the section #data in ( ) and ‘ ‘.

So hats off to Nicola Strappzaaon and all the other new bloggers on Planet! And I encourage everyone to check out the different language blogs as you never know what gems you will find there.

MySQL Marinate for the Holiday Season

October 27, 2014

Just a friendly reminder that you can pick up MySQL Marinate whenever you want! You can use the master list at http://www.meetup.com/Virtual-Tech-Self-Study/messages/boards/thread/38423162 for reference – just ignore the dates and work at your own pace!

We found that very few people were taking the dates to heart, so we stopped trying to organize around them. The message boards are still valid, so feel free to ask if you have any questions – we are here to help!

The above was a quick note from Sheeri Cabral about the wonderful MySQL Marinate program that arrived in my email. This is a great way for novices to learn MySQL and for old war horses to find some new insights.