SQL

For a little side project I wanted an easy way to perform regular backups of a MariaDB database and upload the resultant dump gzipped to S3. Here are the steps to make this happen. The setup Docker container running MariaDB Docker engine running on a AWS EC2 instance An S3 bucket as the destination for the dumps Writing the backup script We begin with writing our shell script, backup.sh, which we will later execute in regular intervals from our host: ...

When searching for partial strings in MySQL with LIKE you will match case-insensitive by default*. SELECT name FROM users WHERE name LIKE 't%' +--------------------+ | name | +--------------------+ | Test | | test | +--------------------+ If you want to match case-sensitive, you can cast the value as binary and then do a byte-by-byte comparision vs. a character-by-character comparision. The only thing you need to add to your query is BINARY. ...

Micro tutorial: Sometimes you need to show results from table A but also include the count of related records from table B. For example: how many items are in each purchase order, how many comments in each post, how many galaxies in each universe (wait, what?), … The following query shows one way of selecting the title and id from a table Posts, the count of the related records in a table Comments and then sorts descending by that count. fk_post is the foreign key of the post in the Comments table. ...