It's not possible to use functions in a SQL statement to alter the identifier assigned to a column being returned. The SQL way of specifying the identifier for the column in a resultset is to use the expr AS alias approach.
Rather than trim off the leading digit characters, you could prepend the identifiers with another valid character. (Trimming off leading characters seems like it would potentially lead to another problem, duplicate and/or zero length column names.)
You could just use a SQL statement to generate the SELECT list for you.
(NOTE: the GROUP_CONCAT function is limited by some system/session variables: group_concat_max_len and max_allowed_packet, it's easy enough to adjust these higher, though changing global max_allowed_packet may require MySQL to be restarted.)
To get it back the SELECT list on all one line (assuming you won't overflow the GROUP_CONCAT limits) something like:
SELECT c.table_schema
, c.table_name
, GROUP_CONCAT(
CONCAT('t.`',c.column_name,'` AS `x',c.column_name,'`')
ORDER BY c.ordinal_position
) AS select_list_expr
FROM information_schema.columns c
FROM information_schema.columns c
WHERE c.table_schema = 'mydatabase'
GROUP BY c.table_schema, c.table_name
Or, you could even get back a whole SELECT statement, if you wrapped that GROUP_CONCAT expression (which produces the select list) in another CONCAT
Something like this:
SELECT CONCAT('SELECT '
, GROUP_CONCAT(
<select_list_expr>
)
, ' FROM `',c.table_schema,'`.`',c.table_name,'` t;'
) AS stmt
FROM information_schema.columns c
WHERE c.table_schema = 'mydatabase'
GROUP BY c.table_schema, c.table_name
You could use a more clever expression for <select_list_expr>, to check for leading "digit" characters, and assign an alias to just those columns that need it, and leave the other columns unchanged, though that again introduces the potential for returning duplicate column names.
That is, if you already have columns named '1_X' and 'x1_X' in the same table. But a carefully chosen leading character may avoid that problem...
The <select_list_expr> could be more clever by doing a conditional test for leading digit character, something like this:
SELECT CONCAT('SELECT '
, GROUP_CONCAT(
CASE
WHEN c.column_name REGEXP '^[[:digit:]]'
THEN CONCAT('t.`',c.column_name,'` AS `x',c.column_name,'`')
ELSE CONCAT('t.`',c.column_name,'`')
END
)
, ' FROM `',c.table_schema,'`.`',c.table_name,'` t;'
) AS stmt
FROM information_schema.columns c
WHERE c.table_schema = 'mydatabase'
GROUP BY c.table_schema, c.table_name
Again, there's a potential for generation "duplicate" column names with this approach. The conditional test "c.column_name REGEXP" could be extended to check for other "invalid" leading characters as well.
As a side note, at some point, someone thought it a "good idea" to name columns with leading digit characters. Just because something is allowed doesn't mean it's a good idea.
Then again, maybe all that rigamarole isn't necessary, and just wrapping the column names in backticks would be sufficient for your application.
