How to specify data type on create table as select in mysql?


Problem :

When I run attached example, it creates table with column id and data type bigint(295) I would like to define it as int(11). In more complex query (I've got ids from group concat) I've got text instead of int.

How to specify data type on create table as select in mysql?

DROP TABLE IF EXISTS some_table;
CREATE TABLE IF NOT EXISTS some_table AS (
SELECT
CONVERT("123,456,789,987,654,321,741,852,963,147,258,369,123,456,789,987,654,321,741,852,963,147,258,369
  ,2123,456,789,987,654,321,741,852,963,147,258,369,123,456,789,987,654,321,741,852,963,147,258,369
  ,3123,456,789,987,654,321,741,852,963,147,258,369,123,456,789,987,654,321,741,852,963,147,258,369"
, SIGNED INTEGER) AS id);


Solution :

I can't find straight way to do it. Solve it by alter table, worked me, perhaps there is better solution.

DROP TABLE IF EXISTS some_table;
CREATE TABLE IF NOT EXISTS some_table AS (
SELECT
CONVERT("123,456,789,987,654,321,741,852,963,147,258,369,123,456,789,987,654,321,741,852,963,147,258,369
  ,2123,456,789,987,654,321,741,852,963,147,258,369,123,456,789,987,654,321,741,852,963,147,258,369
  ,3123,456,789,987,654,321,741,852,963,147,258,369,123,456,789,987,654,321,741,852,963,147,258,369"
, SIGNED INTEGER) AS id);

ALTER TABLE some_table CHANGE id id INT( 11 ) NULL DEFAULT NULL ;

Mysql Tutorials

Mysql Howto..

How to convert this MySQL query to a Django query?

How to connect to a MySQL database through ODBC from Qt application?

how to select from two not joined tables in mysql

How to sync two MySQL tables?

How to make mysql query from subqueries combine the 2 results in to 1 result

How to remove duplicates in MySQL database by time difference

How to clean UTF-8 data for MySQL

How to select a row before a specific row on MySQL, if the table is ordered by date?

how to update a
when database have changed, with ajax php and mysql?

How to interface a java application with a local database in Mac os x

How to set up cascading deletes in MySQL workbench?

How to keep the order of MySQL result?

How should I go about transferring a csv file to a database using C#?

How does MySQL use ORDER BY when referencing an indexed prefix on VARCHAR?

how to get last modified column name from mysql

PHP: How to protect digital products(video, audio, ebook, etc.) from unathorized downloading? [duplicate]

How to Delete some files after Expire time (PHP,MySQL)?

How to keep your hibernate levell 1 cache consistent through external updates?

How to convert/cast varchar to date? [closed]

PHP, PDO, MySQL & InnoDB, how to update/delete records with FK?

How to count multiple columns with mysqli?

How hard is it to modify the Django Models?

how to update flag bit in mysql query?

Mysql ,how to add two new columns and insert data at the same time?

How to sort content from a mysql table in different ways without having multiple pages?

how to link tables together using timestamp sql, mysql

How to check for blocks of availability in a MySQL booking database?

How to get data from a mysql database using javascript?

How do I iterate over an array with Ruby so that it saves individual records in MySQL?

Mysql - How to retrieve fields from two tables based on certain criteria?