request alter/change

2 posts / 0 new
Last post
sanjv
sanjv's picture
Offline
Last seen: 2 weeks 3 days ago
Joined: Mar 10 2010
Junior Boarder

Posts: 10

Sanjay Vekaria
request alter/change

Hi,

Would be nice to be able to change a field type (e.g. from varchar to char) after a table is created.

mysql equivalent: alter table xxTablexx change xxFieldxx CHAR(8)

currently having to drop and rebuild the table.

Thanks

jtommaney
jtommaney's picture
Offline
Last seen: 2 weeks 4 days ago
Joined: Oct 27 2009
Expert Boarder

Posts: 58

Jim Tommaney
Re:request alter/change

We do have that on our roadmap, but it is not scheduled in the near term. With the upcoming June/July release, the alter column can be accomplished with a add new column, update new column, drop old column, rename column. Still a work-around, but avoids reloading the table.

Thanks - Jim Tommaney
Chief Product Architect

[code][root@srvprodtest1 jtommaney]# idbmysql jt
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.39 MySQL Embedded / InfiniDB Enterprise Edition 1.1.0-1 Alpha (Commercial)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table foo;
create table foo (col1 char(6)) engine=infinidb;
insert into foo values ('abcde');
desc foo;

alter table foo add column tmp_col varchar(6);
update foo set tmp_col = col1;
alter table foo drop column col1;
alter table foo change tmp_col col1 varchar(6);

desc foo;
Query OK, 0 rows affected (0.39 sec)

mysql> create table foo (col1 char(6)) engine=infinidb;
Query OK, 0 rows affected (0.34 sec)

mysql> insert into foo values ('abcde');
Query OK, 1 row affected (0.15 sec)

mysql> desc foo;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1 | char(6) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.04 sec)

mysql>
mysql> alter table foo add column tmp_col varchar(6);
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> update foo set tmp_col = col1;
Query OK, 1 row affected (0.29 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> alter table foo drop column col1;
Query OK, 0 rows affected (0.32 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table foo change tmp_col col1 varchar(6);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>
mysql> desc foo;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1 | varchar(6) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)
[/code]