Example Codes for Dynamic Column Names/Aliases in MariaDB

2024-07-27

Example Codes for Dynamic Column Names/Aliases in MariaDB

Dynamic Columns (MariaDB 10.0+)

This example showcases adding and retrieving values from dynamic columns:

-- Create a table with a dynamic column
CREATE TABLE my_table (
  id INT PRIMARY KEY,
  data BLOB
);

-- Add a dynamic column named "score" with value 100
UPDATE my_table
SET data = COLUMN_ADD(data, "score", 100)
WHERE id = 1;

-- Retrieve the value of the "score" column
SELECT id, COLUMN_GET(data, "score" AS INT) AS score
FROM my_table;

Dynamic SQL Construction

This example builds a query with a dynamic alias based on a variable:

-- Define the variable for the alias
SET alias_name = 'user_age';

-- Construct the query with dynamic alias
SELECT id, name, `alias_name` AS age
FROM users;

Note:

The first example requires MariaDB version 10.0 or later due to the COLUMN_ADD function.
In the second example, remember to escape any user-provided data within the string concatenation to prevent SQL injection vulnerabilities.

While not truly dynamic column names, you can use CASE expressions to conditionally select values based on a variable or expression, mimicking a dynamic column.

SET column_name = 'age';

SELECT id, name,
  CASE WHEN column_name = 'age' THEN birth_year ELSE NULL END AS age
FROM users;

This approach works well for a limited number of pre-defined options.

Subqueries:

You can use subqueries to retrieve data from another table and project it as a new column with a desired alias. This is helpful when the dynamic column data resides elsewhere.

SELECT id, name,
  (SELECT age FROM user_details WHERE user_details.user_id = users.id) AS age
FROM users;

This allows flexibility but can become complex for intricate logic.

Information Schema Views:

MariaDB provides information schema views like INFORMATION_SCHEMA.COLUMNS that contain information about the tables in the database. You can join these views with your main table and filter based on specific criteria to achieve a dynamic effect.

This method requires more advanced understanding of the information schema but offers powerful control over retrieving column details.

mariadb