Change column data type while creating table in mysql

Question

I am creating new table from existing table using select query. The existing table column having data type varchar(255) so in newly created table that column contains varchar data type as default.

Now how to change new table column to other data type TEXT. here is the mysql query. Tried cast() and convert() but didn't work for TEXT data type

CREATE TABLE topper  AS
SELECT studentname as `Name`, rollno as `Roll_No`, description as `Information`
FROM student

I want 'Information' column in TEXT data type.

Thanks for any help.

Why not change the column type afterwards using ALTER TABLE? — Nico Haase
– Nico Haase, Commented Sep 18, 2020 at 12:50
Or use the documented steps at dev.mysql.com/doc/refman/8.0/en/create-table-select.html? — Nico Haase
– Nico Haase, Commented Sep 18, 2020 at 12:51
To be specific, from the manual page linked by @NicoHaase, "The data type of SELECT columns can be overridden by also specifying the column in the CREATE TABLE part." — Nick
– Nick, Commented Sep 18, 2020 at 12:55
I disagree this question lacks clarity. Questions don't have to be complex to be clear. — O. Jones
– O. Jones, Commented Sep 18, 2020 at 13:02

Rick James · Accepted Answer · 2020-09-19 23:50:44Z

TEXT is essentially a variant on VARCHAR(...).

CREATE TABLE topper (
    Information TEXT NOT NULL,
) AS
SELECT studentname as `Name`, rollno as `Roll_No`, description as `Information`
FROM student;

Note that you can specify columns, indexes, etc inside the CREATE. The processing will match up the columns by name, then fill in the rest of the columns.

Or...

After you have a table with x VARCHAR(255) NOT NULL, you can change that to TEXT via

ALTER TABLE t
    MODIFY COLUMN x TEXT NOT NULL;

You can go from VARCHAR to TEXT or vice versa; just be aware that, since they have different 'max size', you could get truncation. Do SHOW WARNINGS; after running the ALTER.

Also, be sure to include all the extra clauses (NULL, CHARSET, etc).

Another way to think about these and similar issues is that logically, the data is dumped as strings, then inserted. That is, whatever conversions work to/from strings will be applied. Effectively, no CAST/CONVERT is needed, at least not between TEXT and VARCHAR.

O. Jones · Accepted Answer · 2020-09-18 12:56:14Z

0

First do CREATE TABLE, then do INSERT SELECT. Something like this.

CREATE OR REPLACE TABLE topper (
    Name        VARCHAR(255),
    Roll_No     INT,
    Information TEXT
);

INSERT INTO topper (Name, Roll_No, Information)
     SELECT studentname as `Name`, rollno as `Roll_No`, description as `Information`
       FROM student;

This is good practice because it lets you be explicit about the new table's data types as you asked. It's also good because it lets you specify things like indexes, primary keys, and collations in the new table.

answered Sep 18, 2020 at 12:56

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Collectives™ on Stack Overflow

Change column data type while creating table in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related