MySQL Forums
Forum List  »  MyISAM

'create table ... select' creates text column where varchar would suffice
Posted by: A. Valkenberg
Date: June 11, 2014 01:33AM

Hello everyone :)

I have noticed some unexpected behaviour from the MyISAM engine, while using the 'create table ... select' syntax.

The problem is that I concat two VARCHAR(255) columns and an INT(11) column with some extra chars to create a column in the to-be-created-table.
This concat, which looks like:
CONCAT( first_column, ' - ', second_column, ' (', third_column, ')') AS x
where first_column and second_column are both VARCHAR(255) and third_column is an INT(11), causes the 'x' column in the created table to have the TEXT datatype.

The 'x' column should be able to fit into a VARCHAR(...) datatype.
Take, for example, the following concat-statement:
CONCAT( first_column, ' (', third_column, ')') AS y
which creates a VARCHAR(269) column as expected.

I am wondering why MyISAM decides to move to a TEXT datatype for the created column instead of a VARCHAR(...) datatype.
Defining the datatype for the column in the 'create table ... select' statement, before the select-keyword (as explained here: http://dev.mysql.com/doc/refman/5.6/en/create-table-select.html), is a workaround/solution, but I would prefer to understand why this happens, to prevent future issues.

Thanks in advance to anyone who might shed some light on this for me.

Options: ReplyQuote


Subject
Views
Written By
Posted
'create table ... select' creates text column where varchar would suffice
2776
June 11, 2014 01:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.