The idea of ACID transactions are a basic feature of SQL’s individual Data Manipulation Language (DML) commands, like the INSERT, UPDATE, and DELETE statements. Transactions across two or more tables are a natural extension of ACID compliance features provided by DML commands. However, they require a structured programming approach, like a store procedure or like API implemented in an imperative language.

Surprisingly, transaction management wasn’t covered well in Alan Beaulieu’s Learning SQL because he only provided pseudo code logic. While I thought troubleshoot some broken MySQL SQL/PSM logic would be a good learning experience for students, it wasn’t. So, I wrote this sample code to show how to achieve an all or nothing transaction across four tables.

The code for this example on transaction management lets you perform the important tasks necessary to effect transaction …

Introduction In this article, I’m going to explain how to do query profiling using the MySQL Performance Schema. The Performance Schema has been available since MySQL 5.5.3 and allows MySQL to instrument SQL queries and store their trace information in various tables that you can later inspect in order to determine why a given SQL statement is slow. On older versions of MySQL, you might have used the SHOW PROFILE command, but since this feature has been deprecated, you should now use the Performance Schema for SQL query profiling. Enabling the MySQL... Read More

The post MySQL Query Profiling Using Performance Schema appeared first on Vlad Mihalcea.

Most developers use some form of auto-incrementing integer counter for a given database table, ensuring uniqueness among the rows. Several of the popular SQL dialects implement this facility. For instance, MySQL’s AUTO_INCREMENT attribute is used to provide a unique identity for a table row. What exactly is the behavior of AUTO_INCREMENT? Can you explicitly use a value of your choosing for it if you need to? How does it count? Continue reading and know the answers to these questions and more…

Image by Gerd Altmann from …

CSV imports with MySQL Workbench, is super simple. Since CSV’s are probably the most common data interchange format, it goes without saying that importing CSV data into MySQL is a staple task for all DBA’s and Developers. Continue reading to learn how easy it is using MySQL Workbench…

Image by OpenClipart-Vectors from Pixabay

Self-Promotion:

If you enjoy the content written here, by all means, …

Some believe the most important part of SQL is the ability to query data. Queries typically retrieve data by joining many tables together into useful result sets. This tutorial takes the position that visibility into the data helps those new to SQL understand how joins work. To that end, the queries use Common Tabular Expressions (CTEs) instead of tables.

Default behavior of a JOIN without a qualifying descriptor is not simple because it may return:

• A CROSS JOIN (or Cartesian Product) when there is no ON or USING subclause, or
• An INNER JOIN when you use an ON or USING subclause.

The following query uses JOIN without a qualifier or an ON or USING subclause. It also uses two copies of the single CTE, which is more or less a derived table and the result of a subquery held in memory. This demonstrates the key …

MySQL membership conditions are in the MySQL 8 Documentation. They’re found in the 13.2.11.3 Subqueries with ANY, IN, or SOME section. The IN and =ANY operators both perform equality matches with one twist. The IN operator works with a set of values or a subquery but the =ANY operator only works with a subquery.

I created the digits, letters, and words tables for this example. They hold the following values respectively:

• The numbers table holds the values of 1, 2, 3, and 4
• The letters table holds the values of 'a', 'b', 'c', and 'd'
• The words table holds the values of 'Captain America', 'Iron …

I recently published a blog post about a portfolio project I am developing using the LAMP stack, Bootstrap 4, jQuery, and the MVC (Model-View-Controller) design pattern in core PHP. In this post, I will introduce an additional feature I integrated into the existing project.

Image by xresch from Pixabay

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and …

While you can download MySQL as a DMG package, a number of users would prefer to install it as a Docker instance. You won’t find the macOS downloads on the same web site as other downloads. You can use the following macOS download site.

After installing Docker on your macOS, you can pull a copy of the current MySQL Server with the following command:

docker pull mysql/mysql-server

You should create a mysql directory inside your ~/Documents directory with this command:

mkdir ~/Documents/mysql

Then, you should use the cd command to change into the ~/Documents/mysql directory and run this command:

pwd

It should return the following directory: …

Introduction Welcome to a new issue of the High-Performance Java Persistence Newsletter in which we share articles, videos, workshops, and StackOverflow answers that are very relevant to any developer who interacts with a database system using Java. Articles From version 2.12, Percona PMM uses Victoria Metrics instead of Prometheus. Victoria Metrics provides better disk I/0 utilization and less memory usage. For more details about this change and its benefits, check out this article. By default, the MySQL JDBC Driver only emulates prepared statements. If you wonder whether server-side prepared statements perform better... Read More

The post High-Performance Java Persistence Newsletter, Issue 22 appeared first on …

If you use SQL on a regular basis, then you are well aware that Window Functions are powerful. They allow us to simplify queries that would otherwise be quite the mess. We can provide meaningful insight across rows of data without collapsing the results into a single value. I have written numerous blog posts on Window Functions, many here recently. I decided to make this blog post a compilation of all the Window Function posts I have written, providing a one-stop source for any readers interested in learning more about Window Functions…

Image by Free-Photos from …