How to emulate the TYPEOF() function in MySQL
Want to know the type of an arbitrary expression in MySQL? Someday in the far far future in version 7.1, you might be able to with the TYPEOF() function.
For now you can try this:
CREATE TEMPORARY TABLE typeof AS SELECT [expression] AS col;
For example, let’s see what the type of CRC32 is.
mysql> CREATE TEMPORARY TABLE typeof AS SELECT CRC32('hello world') AS col;
mysql> DESCRIBE typeof;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col | int(10) unsigned | NO | | 0 | |
+-------+------------------+------+-----+---------+-------+
This is one possible way to programmatically determine the type of an expression — even an arbitrarily complex one.
Not beautiful, but it might get the job done. Other ideas?
Further Reading:
You can also start mysql command line client with –column-type-info (-T in old versions). It’ll show correct values as reported by the protocol.
Sergei Golubchik
14 Aug 08 at 4:08 am
Good to know! I was also going to dig into the DBI and DBD::mysql docs and see if there is a way to get the info from the protocol too.
Xaprb
14 Aug 08 at 8:17 am
Hmm, my mysql client doesn’t have such an option. Looks like it’s new in 5.1.
Xaprb
14 Aug 08 at 8:35 am
Oh, right — before 5.1 it is named –debug-info/-T.
Xaprb
14 Aug 08 at 8:36 am
And as hoped, I found the desired information is exposed through the DBD::mysql driver. You can do a bunch of things like
$sth->{mysql_type_name}To get an arrayref of column types for the executed statement.
Xaprb
16 Aug 08 at 12:57 pm