MySQL Perspective

@DerickDowney discussed this in his post INT(5) vs SMALLINT(5). He quoted from the MySQL Docs:

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

Given this statement, if you create a table with a column type INT it will appear as INT(11). Why 11 ? INT has a range of -2147483648 and 2147483647. It takes 11 spaces to display the smallest signed INT -2147483648. Nothing more, nothing less.

Character data is entirely different story. Defining a VARCHAR requires the table header knowing the maximum length in advance. Rather than go into a lengthy explanation, please read the following posts:

• Fine Tuning The Table Definition (Percona)
• InnoDB Physical Row Structure (MySQL Documentation)

From the information in these posts, I would confidently say that you have no choice but to define a length for a VARCHAR. If you not sure, just define all VARCHARs as VARCHAR(255). No wasted is there. Alter loading data you could always run the following

SELECT varcharcolumnname FROM yourtable PROCEDURE ANALYSE();

and let it tell you the proper VARCHAR size.

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).

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;