Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts

    force column size

    Code:
    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 ?
    Last edited by BubikolRamios; 03-23-2013 at 11:09 AM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    ???

    Code:
     
    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??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    But I don't know how you could change (for example) INT to TINYINT, etc.

    Only works for VARCHAR() I think.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •