Open acharis opened 11 years ago
Source MySQL table:
CREATE TABLE `t1` (`f1` SMALLINT, `f2` MEDIUMINT, `f3` INT, `f4` BIGINT);
will result in the following DDL:
CREATE TABLE "t1" ("f1" SMALLINT, "f2" INTEGER, "f3" INTEGER, "f4" BIGINT);
NuoDB types have always sign (UNSIGNED modifier is not allowed), therefore allowed ranges for MySQL UNSIGNED and NuoDB "signed" types will be different:
Column | MySQL Type | MySQL Range | NuoDB Type | NuoDB Range |
---|---|---|---|---|
f1 | SMALLINT UNSIGNED | [0,65535] | SMALLINT | [-32768,32767] |
f2 | MEDIUMINT UNSIGNED | [0,16777215] | INTEGER | [-2147483648,2147483647] |
f3 | INT UNSIGNED | [0,4294967295] | INTEGER | [-2147483648,2147483647] |
f3 | BIGINT UNSIGNED | [0,18446744073709551615] | BIGINT | [-9223372036854775808,9223372036854775807] |
NuoDB migrator converts each MySQL UNSIGNED type to the closest NuoDB numeric type, so that value loss & numeric overflow will not happen during data migration. Consider another MySQL table (note UNSIGNED modifier):
CREATE TABLE `t2` (`f1` SMALLINT UNSIGNED, `f2` MEDIUMINT UNSIGNED, `f3` INT UNSIGNED, `f4` BIGINT UNSIGNED);
INSERT INTO `t2` VALUES (65535, 16777215, 4294967295, 18446744073709551615);
will be converted to NuoDB table:
CREATE TABLE "t2" ("f1" INTEGER, "f2" INTEGER, "f3" BIGINT, "f4" NUMERIC(21));
-- table data will be migrated properly using bin/nuodb-migrator dump [...] & bin/nuodb-migrator load [...] commands
mysql: bigint(20) unsigned not null auto_increment
got converted to: numeric(21) GENERATED BY DEFAULT AS IDENTITY("SEQ_asdf") NOT NULL