...

View Full Version : force column size



BubikolRamios
03-23-2013, 11:03 AM
create destTable t as select column from sourceTable


the type & size of column in destTable is determined by mysql and is not necessary same as in sourceTable, besides it can be calculated, ....

Is there a way to force type & size od resulting column in destTable, inside upper sql (excluding option to create destTable before inserting from sourceTable)?

Say by default mysql creates varchar(200), but I want varchar(2) , coz I know for sure there is nothing inside sourceTable column but 2 digit numbers ?

Old Pedant
03-24-2013, 04:04 AM
???



mysql> create table oldtable ( num int, str varchar(200) );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into oldtable values( 123, 'something string' );
Query OK, 1 row affected (0.02 sec)

mysql> select * from oldtable;
+------+------------------+
| num | str |
+------+------------------+
| 123 | something string |
+------+------------------+
1 row in set (0.00 sec)

mysql> describe oldtable;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num | int(11) | YES | | NULL | |
| str | varchar(200) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> create table newtable AS select num, left(str,5) AS newstr from oldtable;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from newtable;
+------+--------+
| num | newstr |
+------+--------+
| 123 | somet |
+------+--------+
1 row in set (0.00 sec)

mysql> describe newtable;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| num | int(11) | YES | | NULL | |
| newstr | varchar(5) | YES | | NULL | |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Is that what you mean??

Old Pedant
03-24-2013, 04:05 AM
But I don't know how you could change (for example) INT to TINYINT, etc.

Only works for VARCHAR() I think.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum