1

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 ?

  • 1
    Aren't the int, varchar, tinyint good for you? Why do you need some numbers instead? – ypercubeᵀᴹ May 8 '15 at 15:45
  • @ypercube I actually have to incorporate this query into a .net application and currently I am using numbers. I will change the code once people here tell me that's impossible. – Name changed to mask identity May 8 '15 at 15:46
  • 1
    I don't think that mysql exposes any internal numbers/codes to information schema. – ypercubeᵀᴹ May 8 '15 at 15:50
  • Such is the cost of porting from one brand of RDBMS to another. – Rick James May 8 '15 at 20:03
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.