MySQL Temporal Tables, also known as System-Versioned Tables, are a feature introduced in MySQL 5.7. Temporal tables allow you to store historical data and track changes over time automatically. Each row in a temporal table has a period of validity during which it is considered valid.
Temporal tables consist of two tables: a current table and a history table. The current table contains the current version of the data, while the history table stores the historical versions. MySQL manages the history table automatically, ensuring that changes to the current table are recorded in the history table.
To enable temporal tables, you need to define the tables with a special syntax. Let's take an example of creating a temporal table called "employees" to store employee data:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10,2),
valid_from DATETIME(6) NOT NULL,
valid_to DATETIME(6) NOT NULL,
PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) ENGINE=InnoDB;
In the above example, the valid_from and valid_to columns are used to define the validity period of each row. The PERIOD FOR SYSTEM_TIME clause specifies the columns that define the period.
To insert data into a temporal table, you can use regular INSERT statements:
INSERT INTO employees (id, name, salary, valid_from, valid_to)
VALUES (1, 'John Doe', 5000.00, '2023-01-01', '2023-07-05'),
(2, 'Jane Smith', 6000.00, '2023-01-01', '2023-07-05');
The above INSERT statement inserts two rows into the employees table, each with a specified validity period.
To query the current data from the temporal table, you can use regular SELECT statements:
SELECT * FROM employees;
This will return the current versions of the rows in the table.
To retrieve historical versions of the data, you can use the FOR SYSTEM_TIME clause in your SELECT statement:
SELECT * FROM employees FOR SYSTEM_TIME AS OF '2023-06-30';
The above query retrieves the versions of the rows as they were on June 30, 2023.
Now, let's see an example of using MySQL temporal tables in PHP. Assuming you have a connection to the MySQL database established, you can execute SQL statements using the mysqli extension:
$mysqli = new mysqli("localhost", "username", "password", "database");
// Enable system versioning for the employees table
$query = "ALTER TABLE employees ADD SYSTEM VERSIONING";
$mysqli->query($query);
// Insert a new employee
$query = "INSERT INTO employees (id, name, salary, valid_from, valid_to)
VALUES (3, 'Michael Johnson', 7000.00, '2023-01-01', '2023-07-05')";
$mysqli->query($query);
// Retrieve current employees
$query = "SELECT * FROM employees";
$result = $mysqli->query($query);
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Salary: " . $row['salary'] . "<br>";
}
// Retrieve historical employees
$query = "SELECT * FROM employees FOR SYSTEM_TIME AS OF '2023-06-30'";
$result = $mysqli->query($query);
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Salary: " . $row['salary'] . "<br>";
}
In the above example, we first enable system versioning for the employees table using an ALTER TABLE statement. Then we insert a new employee and retrieve the current and historical versions of the employees using SELECT statements with the FOR SYSTEM_TIME clause. The retrieved data is then displayed using a simple loop.
Note: The example assumes you have already established a valid database connection using the appropriate credentials.
That's an overview of MySQL temporal tables and an example of using them in PHP. Temporal tables provide a convenient way to store historical data and track changes over time in MySQL databases.