AboutI was perusing the new questions on dba.stackexchange.com when I happened upon a question on How to see special characters in MySQL Workbench query results. Intrigued, I checked it out. The user was looking for a way to show trailing whitespace characters in order to see which values had extra spaces. Trailing whitespace is not always a bad thing; depending on what the data is used for, they may be perfectly acceptable. That got me thinking, how does one view these extra space characters? Since I could not find an option to do that in any of my GUI MySQL tools, I decided to follow the user's own workaround and see what it might take to inject the quotes within a SELECT statement.
It's fairly trivial to do so using the CONCAT() function as long as you know which fields you want to enclose in quotes. Coming up with a more generic solution takes a little more doing. In this tutorial, we'll look at two approaches, one employing a user function, the other, using a stored procedure.
Solution 1: Using a Function
The INFORMATION_SCHEMA COLUMNS table contains information about every column of every schema. It's referenced by the SHOW COLUMNS statement. Here's the output for the actor table of the Sakila sample database:
Field Type Null Key Default Extra ----------------------------------------------------------------------------------------------- actor_id smallint(5) unsigned NO PRI auto_increment first_name varchar(45) NO last_name varchar(45) NO MUL last_update timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
You can query the COLUMNS table directly to obtain specific information, such as the data type. The following user function accepts the table and column names to look up the column type:
CREATE DEFINER=`root`@`localhost`
FUNCTION `get_column_type`(p_table_name VARCHAR(50), p_column_name VARCHAR(50))
RETURNS varchar(20) CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE p_column_type VARCHAR(20);
SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'sakila'
AND TABLE_NAME = p_table_name
AND COLUMN_NAME = p_column_name into p_column_type;
RETURN p_column_type;
END
We can then call get_column_type() for any field that we might want to modify based on its type:
SELECT
CASE
WHEN get_column_type('actor', 'actor_id') = 'VARCHAR' THEN
CONCAT('"',actor_id,'"')
ELSE
actor_id
END AS actor_id,
CASE
WHEN get_column_type('actor', 'first_name') = 'VARCHAR' THEN
CONCAT('"',first_name,'"')
ELSE
first_name
END AS first_name,
CASE
WHEN get_column_type('actor', 'last_name') = 'VARCHAR' THEN
CONCAT('"',last_name,'"')
ELSE
last_name
END AS last_name
FROM actor;
Here are the first five rows produced by the above query:
actor_id first_name last_name ------------------------------------- 1 "PENELOPE" "GUINESS" 2 "NICK" "WAHLBERG" 3 "ED" "CHASE" 4 "JENNIFER" "DAVIS" 5 "JOHNNY" "LOLLOBRIGIDA"
Solution 2: Using a Stored Procedure
The function approach works well enough for simple queries against smaller datasets, but calling the function for each column could get tedious fast. What we need is a stored procedure that can iterate over table columns and call the function for us!
Here is the basic proc. I'll explain a few things after the code:
CREATE DEFINER=`root`@`localhost`
PROCEDURE `varchar_select`(IN p_schema_name varchar(50),
IN p_table_name varchar(50))
READS SQL DATA
BEGIN
DECLARE p_sql VARCHAR(255) DEFAULT "SELECT ";
DECLARE num_rows INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE col_name VARCHAR(50);
DECLARE col_type VARCHAR(50);
DECLARE col_names CURSOR FOR
SELECT column_name, column_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = p_schema_name
AND table_name = p_table_name
ORDER BY ordinal_position;
-- be sure to open the cursor before calling FOUND_ROWS()!
OPEN col_names;
select FOUND_ROWS() into num_rows;
SET i = 1;
the_loop: LOOP
IF i > num_rows THEN
CLOSE col_names;
LEAVE the_loop;
END IF;
FETCH col_names INTO col_name, col_type;
SET p_sql = CONCAT(p_sql, col_name, ',');
SET i = i + 1;
END LOOP the_loop;
-- remove the trailing comma from the field list
SET p_sql = TRIM(TRAILING ',' FROM p_sql);
-- create and execute the prepared statement
SET @sqlv = p_sql;
PREPARE stmt1 FROM @sqlv;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END
The above version of the proc iterates over the column names and concatenates them to the SELECT string that will be executed as a prepared statement. Now, we'll add the code to enclose the VARCHAR values within quotes.
The Updated Call to Concat()
The col_type includes the length of varchar fields so that it may come back as varchar(50), so a LIKE expression is employed for type matching.
The 2nd CONCAT parameter is a string that calls CONCAT() from the prepared statement. Hence, it produces a string such as CONCAT('"', col_name, '"') AS col_name,.
SET p_sql = IF(col_type LIKE 'varchar%',
CONCAT(p_sql, 'CONCAT(\'"\',',col_name, ',\'"\') AS ',col_name, ','),
CONCAT(p_sql, col_name, ',')
);
Calling the varchar_select Proc
Calling our proc for the actor table does exactly what we hope it would; the name fields are enclosed within quotes, while the other fields are not:
mysql> call varchar_select('sakila', 'actor');
actor_id first_name last_name last_update
-------------------------------------------------------------
1 "PENELOPE" "GUINESS" 2006-02-15 04:34:33
2 "NICK" "WAHLBERG" 2006-02-15 04:34:33
3 "ED" "CHASE" 2006-02-15 04:34:33
4 "JENNIFER" "DAVIS" 2006-02-15 04:34:33
5 "JOHNNY" "LOLLOBRIGIDA" 2006-02-15 04:34:33
The full function and proc code is available on GitHub.
Going Forward
While we have achieved something fairly generic today, there are still some limitations to our proc. For one thing, it always returns all of the table columns. Moreover, there is no support for joins at all. In the next installment, we'll take a parameterized query approach to handle more complex queries.