Skip to content

Preprocessing

MatrixOne provides support for server-side preprocessing statements. Taking advantage of the efficiency of the client or server binary protocol, the parameter values ​​are preprocessed using placeholders. The advantages during execution are as follows:

  • The efficiency of parsing statements is improved every time the statement is executed. Typically, database applications handle a large number of nearly identical statements, changing only the literal or variable values ​​in clauses, such as WHERE for querying and deletion, SET for updates, and VALUES for insertions.

  • Prevent SQL injection. Parameter values ​​can contain unescaped SQL quotes and separators, compile at one time, run multiple times, eliminating the process of parsing and optimization.

PREPARE, EXECUTE, and DEALLOCATE PREPARE statements

The basic SQL syntax of PREPARE statements is mainly the following three types of SQL statements:

Create preprocessing statements

PREPARE stmt_name FROM prepared_stmt
Parameters Description
stmt_name Name of precompiled SQL statements
preparable_stmt Stand literals or user variables containing SQL statement text. The text must represent a single statement, not multiple statements.

Execute preprocessing statements

EXECUTE stmt_name [USING @var_name [, @var_name] ...]
Parameters Description
stmt_name Name of precompiled SQL statement

Delete preprocessing statements

{DEALLOCATE | DROP} PREPARE stmt_name
Parameters Description
stmt_name Name of precompiled SQL statement

Example

-- Create a table
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50)
);

-- Insert data
INSERT INTO customers (id, name, email)
VALUES (1, 'John Doe', 'john@example.com'),
       (2, 'Jane Smith', 'jane@example.com'),
       (3, 'Mike Johnson', 'mike@example.com');

-- Perform preprocessing
mysql> PREPARE stmt FROM 'SELECT * FROM customers WHERE id = ?';
Query OK, 0 rows affected (0.02 sec)

-- Perform preprocessing
mysql> SET @id = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt USING @id;
+------+-----------------------------------------------------------------------------------------------------------------------
| id | name | email |
+------+-----------------------------------------------------------------------------------------------------------------------
| 2 | Jane Smith | jane@example.com |
+------+-----------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

-- Delete preprocessing
mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

The above example first creates a table named customers, which contains three columns id, name, and email. Next, three pieces of data are inserted into the table.

Then, use the PREPARE statement for preprocessing and save SELECT * FROM customers WHERE id = ? as the preprocessing statement in stmt.

When performing preprocessing, set the @id variable to 2 and perform preprocessing using the EXECUTE statement, passing @id as a parameter to the preprocessing statement.

Finally, use the DEALLOCATE PREPARE statement to delete the preprocessing and release the relevant resources.