[FIXED] How to write 'create table ... as select [some number/function] as test' in MySQL 5.5 so that the table gets a column of data type int(11)?

Issue

I build the empty frame of a table in MySQL by taking a huge query, adding a WHERE 1 = 0 condition (to shrink down the output rows to 0) and running it like the following tiny example:

create table xzy as 
select convert(2147483647, signed) as test
WHERE 1 = 0

I thought taking the highest possible integer number would make it an int(11), but it becomes an int(10), and it stays an int(10) when I add just +1 so that it is 2147483648. In my next higher steps, I already landed at bigint, which is not the aim, therefore I started with convert(1, int). Mind that cast(1 as integer) does not seem to be known in legacy MySQL 5.5 db. Then going over to convert(1, signed) made it int(1) only, as if the convert did not change anything. Thus it seems to depend on both number and/or conversion. Or it is perhaps not even possible since I cannot set the length of "signed", it seems.

Now I do not want to try around any further only to find int(11).

I search for the right number/convert parameter that makes the "?" in select convert(?, signed) an int(11) so that I do not need to alter the table afterwards to int(11) for that column.

I do this to avoid new code lines. Most of the numeric columns are int(11), therefore the question.

How can

• int(11) = int(11, signed) and
• int(11, unsigned) be built just by a create table ... as select command?

Solution

Not really sure what the WHERE 1 = 0 is all about? ... so I'm ignoring that part.

Anyway, add a + 1 after the convert achieves the desired goal.

create table xzy as 
select convert(2147483647, unsigned) + 1 as test

Information about that was found around the middle of the page here.
(their example used a bigint value)

Here's a dbfiddle (using verson 5.5) with example use.

Answered By - Paul T.

Answer Checked By - Pedro (PHPFixing Volunteer)