Category: MySql

I thought I’d share some bits and pieces about getting started with Azure Database for MySql, the Azure platform-as-a-service (Paas) offering for the MySql relational database engine. Initially I started looking for a good sample database to use for demonstration purposes, but unfortunately either my search engine skills or my patience were lacking. However, that gave me a good opportunity to set up a new demo database from scratch!

I started out by setting up a dedicated resource group to use for my instance, and then used the Azure Portal GUI to create a new instance named mysql-20200505. I made sure to downgrade from the default General Purpose configuration to Basic, so it will only cost me about $67 a month if I leave it running instead of around $350. After the instance was created successfully I then added some connection security rules to ensure that only my IP was able to connect to it.

I then opened up MySql Workbench to connect to the server as pictured below. Of course it took me two tries to connect as I made my usual error of not including the machine name in the username field the first time around.

I quickly created a new database:

CREATE DATABASE demo;

I then queried the system catalog to make sure that the database had been created and made available successfully:

The first table I decided to build was a tally table – a table of sequential numbers. I placed this in a new schema named admin, and I’ll use it to populate some of the other tables I’ll be creating in the future. I used a set-based method to generate this data by cross-joining one of the system catalog tables to itself. There are about 300 records in the catalog table in a brand new database, and so one cross join gives me 300 x 300, or 90,000 rows, which is plenty more than the 10,000 rows I want.

The only gotcha here was that I’d installed an older version of MySql from before it implemented the ROW_NUMBER() windowing function. Luckily my search engine skills had improved and I found a solution quite quickly using a local variable as below:

USE demo;

CREATE SCHEMA admin;

CREATE TABLE
    admin.tally
(
    SequenceNumber SMALLINT NOT NULL PRIMARY KEY
);


SET @row_number = 0;

INSERT INTO
    admin.tally
(
    SequenceNumber
)
SELECT
    (@row_number := @row_number + 1) AS SequenceNumber
FROM
    information_schema.tables tbl1
CROSS JOIN
    information_schema.tables tbl2
LIMIT
    10000;

And now my tally table is created, populated, and ready to use in the next blog post in which I’ll begin to create some dummy data warehouse tables for my demo database.

Thanks for following along, and please let me know in the comments below if there are any particular areas of MySql you’d like me to dive into deeper!