Example Codes for Filtering Variables in MySQL/MariaDB

1. SHOW VARIABLES LIKE 'pattern':

This command displays information about system variables based on a pattern. You can replace 'pattern' with a wildcard string to filter the variables. For example:

SHOW VARIABLES LIKE 'port'; // Shows only the variable related to port
SHOW VARIABLES LIKE 'max_connections'; // Shows only the variable related to maximum connections

1. Programmatic filtering:

Here, you can write a query to fetch all variables and then process them within your program to filter based on your criteria. This involves two steps:

a. Fetch all variables:

SELECT * FROM information_schema.VARIABLES;

This retrieves all system variables from the information_schema.VARIABLES table.

b. Process and filter in your program:

After fetching the variables, you can use your programming language (e.g., Python, PHP) to iterate through the results and filter them based on your needs. You can check the variable name or value to determine if it matches your criteria.

Here's a key difference to remember:

• SHOW VARIABLES LIKE 'pattern' is a single MySQL command for basic filtering based on a pattern in the variable name.
• Programmatic filtering offers more flexibility as you can define custom filters and logic within your program using the fetched data.

Example Codes for Filtering Variables in MySQL/MariaDB

-- Show variables containing "user" in the name
SHOW VARIABLES LIKE '%user%';

-- Show only the variable for "max_connections"
SHOW VARIABLES LIKE 'max_connections';

Programmatic Filtering (using Python):

import mysql.connector

# Connect to MySQL server
mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password"
)

mycursor = mydb.cursor()

# Fetch all variables
mycursor.execute("SELECT * FROM information_schema.VARIABLES")
variables = mycursor.fetchall()

# Filter variables by name containing "innodb" (case-insensitive)
filtered_variables = [var for var in variables if "innodb" in var[0].lower()]

# Print filtered variable names and values
for var in filtered_variables:
  print(f"Variable Name: {var[0]} - Value: {var[1]}")

# Close connection
mydb.close()

This example connects to a MySQL server, fetches all variables, and then uses a Python list comprehension to filter the variables where the name (first element in the tuple) contains "innodb" (converted to lowercase for case-insensitive matching). Finally, it iterates through the filtered list and prints the variable name and value.

Note:

• Replace "your_username" and "your_password" with your actual credentials.
• You can modify the filtering condition in the list comprehension to suit your needs (e.g., filter by value instead of name).

1. Stored Procedures:

You can create a stored procedure that retrieves variables based on your filtering criteria. This approach offers reusability and allows for encapsulating complex filtering logic within the stored procedure. Here's a basic example:

DELIMITER //
CREATE PROCEDURE GetFilteredVariables(IN filter_string VARCHAR(255))
BEGIN
  SELECT * FROM information_schema.VARIABLES WHERE VARIABLE_NAME LIKE CONCAT('%', filter_string, '%');
END //
DELIMITER ;

-- Call the procedure with a filter string
CALL GetFilteredVariables('sort%');

This procedure takes a filter string as input and retrieves variables where the name contains the provided string (using wildcards).

1. Information Schema Views:

The information_schema database provides various views that offer information about the database server. You can potentially leverage these views to filter variables indirectly. However, this approach might be less straightforward and require exploration of specific views based on your filtering needs.

Here's an example (be aware, this might not be the most efficient method):

SELECT * FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'VARIABLES'
  AND COLUMN_NAME LIKE '%user%';

This query utilizes the information_schema.KEY_COLUMN_USAGE view to find columns (variables) in the VARIABLES table where the column name contains "user".

Choosing the Right Method:

• For basic filtering based on variable names, SHOW VARIABLES LIKE 'pattern' is a simple and efficient solution.
• If you need more complex filtering logic or reusability, consider using stored procedures.
• Programmatic filtering offers the most flexibility but requires coding in your chosen language.
• Information Schema views might be useful for specific scenarios, but explore them cautiously for filtering variables due to potential inefficiencies.