0

I am using SSMA for MySQL tool to migrate data from MySQL to SQL Server 2016. After migration data completed. The field value of float type from MySQL table is different from SQL Server field. In MySQL table, the value is 90177104, but in SQL Server table, the value is 90177100.

Can anyone please explain why the values are different? Is it a bug of SSMA? If so, is there any workaround to make the two values the same? Thanks!

MySQL table schema:

create table test
(
id int auto_increment
primary key,
value float null
);

# insert data.
insert into test(value) values(90177104);

SQL Server table schema:

create table test
(
id int identity
constraint table_name_pk
primary key nonclustered,
value float default NULL
)
Lei
  • 690
  • 1
  • 7
  • 13
  • Additional information: MySQL version: 5.7.37 SQL Server version: Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) – Lei Apr 15 '22 at 07:08

1 Answers1

1

More a display than storage thing.

drop table if exists t,TEST;
create table t
(
id int auto_increment
primary key,
value float(20,1) null,
val2  float null
);

# insert data.
insert into t(value,VAL2) values(90177104,90177104);

SELECT T.* ,CAST(`VALUE` AS DECIMAL),CAST(`VAL2` AS DECIMAL) FROM T;

# id, value, val2, CAST(`VALUE` AS DECIMAL), CAST(`VAL2` AS DECIMAL)
'1', '90177104.0', '90177100', '90177104', '90177104'
P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • Hi P.Salmon, thanks for your reply. I'm surprised. Could you explain more about the reason why val2 display as 90177100 but not 90177104? Although the field type is float but 90177100 and 90177104 are totally different numbers. – Lei Apr 16 '22 at 10:01
  • floats are APPROXIMATE numbers if you want to use exact numbers use decimal or one of the int varieties..Exactly why mysql and sql-server differ I'm afraid I don't know but you may find it if you search for migration hints tips etc. – P.Salmon Apr 16 '22 at 11:10
  • Thanks, P.Salmon, I understand decimal is more accurate than float. But MySQL database is a part of a legacy project. So we are trying to migrate the data from MySQL to a new sql-server database. Float value problem is really annoying. – Lei Apr 19 '22 at 00:26