With the latest MySQL release (8.0.31), MySQL adds support for the SQL standard INTERSECT and EXCEPT table operators.

Let’s have a look how to use them.

We will use the following table:

CREATE TABLE `new` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `tacos` int DEFAULT NULL,
  `sushis` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

For our team meeting, we will order tacos and sushi’s.

Each record represent the order of each team member:

select * from new;
+----+-------------+-------+--------+
| id | name        | tacos | sushis | …

I continue to dig up and share this simple approach for production data masking via SQL to create testing data sets. Time to codify it into a post.

Rather than generating a set of names and data from tools such as Mockaroo, it is more practical to use actual data for a variety of testing reasons.

The SQL below is a self-explanatory approach of removing Personal Identifiable Information (PII), but keeping data relevant. I use this approach for a number of reasons.

• We are using production data rather than synthetic data. Data volume, distribution, and additional column values are realistic. This is a subset of an example, but dates and locations are therefore realistic
• Indexes (and unique indexes) still work, and distribution across the index is adequate for searching. Technically the index …

As of MySQL v8, we have Common Table Expressions (CTEs) or the WITH clause available. I recently learned how to use the WITH clause in an INSERT statement, similar to INSERT with SELECT. Like always, I enjoy sharing what I learn so let’s all learn together…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

For the sake of simplicity (and my sanity) I’m using this arbitrary sample data:

SELECT *
FROM auto_test;

We can use the WITH clause to generate a …

Leading industry analysts such as Constellation research, Wikibon, Futurum, Moor Insights & Strategy, KuppingerCole Analysts, and DBInsight, had the following to say about the recent MySQL HeatWave Lakehouse announcement.

Three years ago, I learned that due to SREcon, Charity Majors was in Amsterdam. I set up a meeting between Benjamin Tyler, Yves Orton and a few more colleagues of mine, and her. That is, because apparently in a case of co-evolution, our company internal “Events” system and Honeycombs observability tooling, modelled after experiencing Fabooks “Scuba” seemed to be doing a lot of the same things.

These days, we are using Honeycomb a lot to record events, and debug code running in distributed systems. But one type of system does not fit into this very well: Databases of all kinds. And I don’t understand why, because it would be perfect.

About one year ago, I wrote an article about “Tracing a single query with PERFORMANCE_SCHEMA

”. Why would I want to trace a single query like that?

…

Once again, the latest version of MySQL 8.0 includes several contributions from the MySQL Community.

MySQL 8.0.31 was released on October 11th 2022.

I would like to thank all contributors on behalf of the entire Oracle MySQL team !

This new release contains patches from Facebook/Meta, Dennis Gao, Lou Shuai, Caza Zhang, Zhang Simon from Tencent, Dimitry Kudryavtsev, Rahul Malik from Percona, Alex Xing, Marcelo Altmann from Percona, Matthew Steeples, Adam Croot, Luis Pinto and Evgeniy Patlan from Percona for all his patches on MySQL Shell.

Here is the list of the above contributions and related bugs:

Replication

• #101056 – Ack_receiver may lost the slave semi-sync ack due to net timeout – Dennis Gao …

It’s hard to believe that OpenLampTech tech will be 1 year in the making and 4 more editions from now. Insane! Here’s this week’s edition with the best PHP, MySQL, and LAMP stack media I curated. Thanks for reading

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

In OpenLampTech issue #48, we are looking at articles covering:

• Preventing race conditions in databases
• Sending emails with CodeIgniter
• Use VIEWS in your Laravel application
• Server-side jQuery Datatables with PHP
• MySQL JSON data type
…

As I teach students how to create tables in MySQL Workbench, it’s always important to review the meaning of the checkbox keys. Then, I need to remind them that every table requires a natural key from our prior discussion on normalization. I explain that a natural key is a compound candidate key (made up of two or more column values), and that it naturally defines uniqueness for each row in a table.

Then, we discuss surrogate keys, which are typically ID column keys. I explain that surrogate keys are driven by sequences in the database. While a number of databases disclose the name of sequences, MySQL treats the sequence as an attribute of the table. In Object-Oriented Analysis and Design (OOAD), that makes the sequence a member of the table by composition rather than aggregation. Surrogate keys are also unique in the table but should never be used to determine uniqueness like the natural key. Surrogate keys are also candidate keys, like a …

No, I don’t think so. But it is does change our profession and have important implications for software engineers using (not managing) MySQL.

No, I don’t think so. But it is does change our profession and have important implications for software engineers using (not managing) MySQL.