1

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?

2
  • I think only VARCHAR requires a length but the info is in MySQL documentation: Datatypes Dec 10 '14 at 15:17
  • If you use CHAR you probably want to declare the max length as well, as the default is CHAR(1). Dec 10 '14 at 15:23
1

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:

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.

1
  • Excellent, this is what I'm looking for. If you look over the list of MySQL types here, are there any others besides CHAR and VARCHAR that require a max length?
    – nhuff717
    Dec 10 '14 at 16:12

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.