MySQL is one of the most popular databases in the world. Completely open-source, it is widely used in Web Development, due to the simplicity of its connection with the most used programming languages in the market. In this article, we will see the main news of version 8 of this database, released in 2018.
1 - Performance
One of the great novelties of version 8 of MySQL is certainly its performance. It is estimated that this version is up to 2x faster than version 5.7 and can reach the impressive mark of up to 1.8 million queries per second. A very significant number, don't you agree?
2 - OpenSSL
Security is a very important topic when new versions of any program are released. With MySQL, it is no different. Version 8 implemented OpenSSL as the default library for its TLS / SSL protocols. This means that this version is even more secure since OpenSSL is a widely used and very reliable library to protect access to the database.
3 - UTF-8
One of the big problems we've always had when working with older versions of MySQL was the issue of encoding and accentuation of words. However, with the launch of version 8, the MySQL team took a big step to help us solve this problem. Now all tables will have utf8mb4 encoding defined as default.
This will greatly facilitate MySQL's communication with programming languages, such as PHP, without us needing to worry about defining UTF-8 in its data.
4 - InnoDB
Another problem that often gave us headaches and that we can now say goodbye to with the launch of version 8 is the issue of transaction errors in MySQL. For them to work well, we need to define the engine of the tables as InnoDB. In previous versions of MySQL, we had to perform this definition on all tables when creating them; and if we forgot any table, the transactions wouldn’t work.
But starting from version 8, this engine is the default for all tables, which is excellent news.
We talk in more detail about transactions in MySQL 8 on our YouTube channel.
5 - JSON
JSON has become almost a universal way to exchange data and information between servers in recent years, being a good successor to XML. And now, this JavaScript object notation is also present in MySQL, as a new data type introduced in version 8. This opens up a range of possibilities related to how to store multiple information in a single column in our database.
To see how it works, how about a small example?
Let's create a users table that will contain the name and photos of the user.
We will use the following script:
CREATE TABLE users (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(256) NOT NULL,
photos JSON NOT NULL,
register TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP()
);
Note that the column “photos” is of type JSON. Let's insert a row into this table, with the following script:
INSERT INTO users VALUES (NULL, 'Djalma Sindeaux', '{"photo1": "images/dj.png", "photo2": "images/perfil.png"}', DEFAULT);
These data will be inserted into our table, including the JSON with the photos of the user. Given this scenario, we could connect this table with a programming language and transform the photos JSON into an array, which would greatly facilitate displaying them to our user.
The most interesting thing about the JSON data type is that it validates the string being provided at the time of insertion. If the JSON is in an invalid format, an error will be returned.
In this article we used the example of a photos JSON, but this new feature can help us in many other situations.
6 - “Reduced SELECT”
The SELECT command is one of the most used when we are working in MySQL. In fact, in version 8, a new feature was introduced that allows us to execute this command with a smaller script.
Now, if we have a command SELECT * FROM, we can simply replace it with TABLE. Note that we are only talking about the SELECT of all table columns.
Let’s use the table we created above as an example. Instead of executing SELECT * FROM users, we can use the script TABLE users. Much more succinct, isn’t it?
However, when we talk about clauses with the TABLE command, so far only the clauses ORDER BY, LIMIT, and OFFSET are supported. This means we cannot use the WHERE clause with TABLE, at least for now.
7 - Window Functions
This novelty helps us solve a very common problem when working with data aggregation. When we perform some type of grouping in MySQL, with commands such as GROUP BY or ORDER BY, for instance, it by default reduces the number of returned rows. The window functions, which were introduced in MySQL version 8, help us see the results more individually in each row of the table.
To see this in practice, let’s do an example. We will create an employees table that will contain the name, salary, and department of each employee. The script to create the table is as follows:
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(64) NOT NULL,
salary DECIMAL(10, 2) NOT NULL,
department VARCHAR(256) NOT NULL,
dtregister TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP()
);
Let’s insert some data into this table using this script:
INSERT INTO employees VALUES
(NULL, 'Bill', 5000, 'Online Courses', DEFAULT),
(NULL, 'Mark', 5000, 'Marketing', DEFAULT),
(NULL, 'John', 5000, 'Development', DEFAULT),
(NULL, 'Leonard', 4000, 'Development', DEFAULT),
(NULL, 'Luke', 3000, 'Marketing', DEFAULT),
(NULL, 'Han', 2000, 'Marketing', DEFAULT),
(NULL, 'Paul', 1000, 'Online Courses', DEFAULT);
Note that we have 7 records in our table. Now let’s perform a SELECT that will return the total salary of employees, grouped by department. We will use the following query:
SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department;
We will see the following result:
The total salary of each department was returned to us. However, if we also want to see the individual salary information of each employee, we can’t do that using the GROUP BY clause. This is when window functions come into play and can help us.
Let’s execute this query again, but now using the OVER clause and instead of using GROUP BY, we will define ORDER BY. The script will look like this:
SELECT `name`, salary, department, SUM(salary) OVER(PARTITION BY department) AS total_salary FROM employees ORDER BY department;
Notice that we will also bring the information of the name and individual salary of each employee. The result will be as follows:
The field “total_salary” still brings the information of the total salary of each department. However, now we can also see what the salary is for each of these employees in those departments. The data is much clearer now, don’t you think?
8 - Invisible indexes
Indexes are widely used to facilitate the location of columns in our tables, which can optimize our queries.
Starting from version 8, MySQL allows us to create invisible indexes. They are normal indexes, but they cannot be seen by the MySQL optimizer. In other words, the index will still exist but cannot be used in any query.
How can this help us? They can be very useful when we want to perform tests on our database. For example: imagine that we want to drop an index, but we want to test if its absence will cause any side effects in our database. In that case, we can define that index as invisible. If any problems arise, just do a “rollback,” so to speak, and define the index as visible again.
Or imagine that we want to add an index to a specific table and want to see if it will help or not with the performance of its queries. We can again use the concept of invisible indexes for that.
We can create an invisible index in three ways: 1) when creating a table; 2) using the CREATE INDEX script and 3) when altering a table.
In all three cases, we only need to add the word INVISIBLE to the index. Example when creating a table:
CREATE TABLE users (
username VARCHAR(256) NOT NULL
INDEX username_idx (i) INVISIBLE
);
Example using the CREATE INDEX command:
CREATE INDEX username_idx ON users (username) INVISIBLE;
Example using the ALTER TABLE command:
ALTER TABLE users ADD INDEX username_idx (username) INVISIBLE;
An invisible index can be changed to VISIBLE at any time. Another important detail to remember is that primary keys cannot be invisible indexes.
9 - Roles
The novelty of roles will be very useful for those who manage access to the resources of the database.
“Roles” means “roles” or “functions.” This feature makes it easier to define privileges for users, as it groups these privileges by the functions each person will have when accessing the database.
Imagine that the employees of our Development Team have three main functions: 1) Database Administrator (DBA); 2) Database Designer and 3) Web Developer. Let’s create three roles for these functions, with the following script:
CREATE ROLE 'hcode_dba', 'hcode_dbdesigner', 'hcode_developer';
Now let's grant privileges to these roles:
GRANT ALL ON hcode_cafe.* TO 'hcode_dba';
GRANT CREATE, DROP, ALTER ON hcode_cafe.* TO 'hcode_dbdesigner';
GRANT SELECT, INSERT, UPDATE, DELETE ON hcode_cafe.* TO 'hcode_developer';
Note that we defined the privileges in the roles performed by the employees, not in the employees themselves. Now let’s create the users for our Team:
CREATE USER 'glaucio'@'localhost' IDENTIFIED BY 'glauciohcode';
CREATE USER 'djalma'@'localhost' IDENTIFIED BY 'djalmahcode';
CREATE USER 'joao'@'localhost' IDENTIFIED BY 'joaohcode';
Finally, let’s link our users with their respective roles:
GRANT 'hcode_dba' TO 'glaucio'@'localhost';
GRANT 'hcode_developer' TO 'joao'@'localhost';
GRANT 'hcode_dbdesigner', 'hcode_developer' TO 'djalma'@'localhost';
Note that in the last line we assigned more than one role to the user ‘djalma’. This illustrates one of the advantages of the roles feature: in addition to helping us better organize user privileges, it allows us to distribute these privileges much more easily. This is a novelty that truly came to assist us.
10 - Deprecated row counting functions
We've reached the last novelty of this article, and we will talk about a deprecation. Using the SQL_CALC_FOUND_ROWS modifier and the FOUND_ROWS() function is a common practice for many of us, especially when we are working with pagination in our systems.
However, it has already been announced by the MySQL team that these two features have been deprecated and will be removed in future versions of MySQL. It may still be working in your MySQL, but soon that will no longer be the case.
There is now a simpler syntax that executes the same function. We will talk about it now. Imagine we want to select only 15 records from our users table that have the name field as “Rafael” and then we want to return the number of records found. In the past we would execute these two scripts:
SELECT SQL_CALC_FOUND_ROWS * FROM users WHERE username LIKE ‘%Rafael%’ LIMIT 15;
SELECT FOUND_ROWS();
This syntax is deprecated. The new syntax involves executing the first query normally, but without the modifier:
SELECT * FROM users WHERE username LIKE ‘%Rafael%’ LIMIT 15;
The second query involves executing the SELECT from the COUNT(*) function, without specifying the table name or the LIMIT, but with the explicit WHERE clause:
SELECT COUNT(*) WHERE username LIKE ‘%Rafael%’;
The returned result will be the same.
If you like, you can follow the demonstration of some of these features in video on Hcode Café #19, our weekly technology program.
These are some of the novelties that came with version 8 of MySQL. However, this version continues to receive updates and new features every year, and we will certainly continue to follow the development of this amazing database management system.
Until the next article :)