MySQL Data Types – Getting Data Type ID in MySQL

MySQLmysql-workbench

So, In SQL, We have the following query to find the system data type ID of columns in SQL Server;

select name, system_type_id from sys.columns where object_id = OBJECT_ID(N'sometable')

It gives the following output;

name       system_type_id
rowid        56
Client       167
IsCurrent    104

The system_type_id represents the int value of data type of that column.

Whereas, when I tried to convert (find an equivalent) in MySQL, I couldn't find the ID of column's data type. The following is the query I have managed to find so far;

SELECT column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'someSchema' AND TABLE_NAME = 'someTable';

It gives the following output.

# column_name,   data_type
    'rowid',      'int'
     'Client',    'varchar'
     'IsCurrent', 'tinyint'

Any suggestions how I can achieve the earlier output in MySQL ?

Best Answer

I discussed How is INFORMATION_SCHEMA implemented in MySQL? about 4 years ago.

SQL and PostgreSQL have ObjectID enumeration (oid) of object types. MySQL made the INFORMATION_SCHEMA database a more simplified representation of table, column, and index metadata. The metadata tables are temporary MEMORY tables with no indexes.

Last year in Adding new tables -- memory usage increases I discussed what happens to memory with the INFORMATION_SCHEMA does when you increase the number of tables.

Believe me, adding an oid for data types to the MySQL INFORMATION_SCHEMA would just bloat the INFORMATION_SCHEMA. In this particular instance, I greatly appreciate MySQL not having oid values exposed.

If you really want to learn about how mysql represents data types under the hood I refer you to pages 48-53 of "Understanding MySQL Internals" (ISBN 0-596-00957-7).

MySQL Internals

Those pages refer to sql/table.h and sql/field.h in the source code. You can download the source code and look it over. Then, judge for yourself how and why oid is not included.

SUGGESTION

Create your own. For example, to make oids for columns do this

DROP DATABASE IF EXISTS my_metadata;
CREATE DATABASE my_metadata;
USE metadata
CREATE TABLE objects ENGINE=MEMORY
SELECT data_type FROM information_schema.columns WHERE 1=2;
ALTER TABLE objects ADD COLUMN oid INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE objects ADD UNIQUE INDEX (data_type);
INSERT INTO objects (data_type)
SELECT DISTINCT data_type FROM information_schema.columns;
Related Question