I am converting a SQL Server table structure to a MySQL table structure, and I need to know how to properly declare the column data types within the CREATE TABLE
statement.
I have a CSV file where each row gives me column info like so:
columnName,sqlServDataType,maxLength,precision,scale,isNullable,isPrimaryKey
idYear,int,4,10,0,0,1
...
I have a file / information on how I will programmatically map the SQL Server data type to the MySQL data type, but I need to know what MySQL data types require the max length declaration in a CREATE TABLE statement.
So for instance, in MySQL you can declare columnName INT
or columnName INT(4)
though there's really no point, from what I've read. However, doing something like VARCHAR(40)
is important for both performance and data insertion concerns. Make a column too big for small data, it's inefficient. Make a column's max length too small for a given chunk of data, and the insertion will fail.
Ultimately for a given MySQL data type, I need to know if the max length e.g. VARCHAR(40)
is required or not. I couldn't find much in the documentation.
Any pointers?
Best Answer
MySQL Perspective
@DerickDowney discussed this in his post INT(5) vs SMALLINT(5). He quoted from the MySQL Docs:
Given this statement, if you create a table with a column type
INT
it will appear asINT(11)
. Why 11 ? INT has a range of-2147483648
and2147483647
. 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:
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
and let it tell you the proper VARCHAR size.