How to compress and decompress data in MySQL?

MySQL is a widely used relational database management system that supports data compression and decompression functions. In large-scale data storage and processing, data compression can significantly reduce storage space usage and improve data transmission efficiency. This article will introduce how to implement data compression and decompression in MySQL, and give corresponding code examples.

1. Compress data

MySQL provides a variety of compression algorithms, such as LZ4, Zlib, Snappy, etc. In MySQL 5.7.17 and later versions, the InnoDB storage engine supports the use of Zlib algorithm to compress row data by default. Data compression can be enabled through the following steps:

1. Configure the compression parameters of InnoDB

In the MySQL configuration file my.cnf or my.ini, find [mysqld] section, and add the following configuration:

[mysqld]
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_file_compression=ON

Among them, innodb_file_format must be set to Barracuda format, and both innodb_file_per_table and innodb_file_compression need to be set to ON. Then restart the MySQL service to make the configuration take effect.

2. Creating a compressed table

Compression can be enabled by using the ROW_FORMAT=COMPRESSED option when creating a table. For example, create a table named employees and compress its row data:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
) ROW_FORMAT=COMPRESSED;

3. Compress existing table

If the table has been created but not enabled Compression, you can use the ALTER TABLE statement to compress the table. For example, to compress the employees table:

ALTER TABLE employees ROW_FORMAT=COMPRESSED;

2. Decompress data

MySQL will automatically decompress the compressed data when querying, and users do not need to perform additional operations when querying.

3. Performance impact of compression and decompression of data

Compression and decompression of data will have a certain impact on system performance. Compression operations consume CPU resources, while decompression operations reduce query performance. Therefore, when using the data compression feature, trade-offs should be made based on the actual situation.

4. Sample code

The following is a sample code that uses MySQL for data compression and decompression:

-- 创建压缩表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
) ROW_FORMAT=COMPRESSED;

-- 插入数据
INSERT INTO employees (id, name, age) VALUES (1, 'John', 25);
INSERT INTO employees (id, name, age) VALUES (2, 'Mary', 30);

-- 查询数据
SELECT * FROM employees;

In the above example, a file named employees is first created. table and compress its row data. Then two pieces of data were inserted and the data was queried through the SELECT statement. MySQL will automatically decompress the compressed data during query and return the original data.

Summary:
In MySQL, you can enable data compression by configuring the parameters of InnoDB and create a compressed table using the ROW_FORMAT=COMPRESSED option. Compressed data is automatically decompressed when queried. In actual use, the impact of compression and decompression on system performance needs to be weighed. The above is an introduction and corresponding code examples on how to implement data compression and decompression in MySQL.

The above is the detailed content of How to compress and decompress data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!