Home > Enterprise >  How to determine the right datatype for columns
How to determine the right datatype for columns

Time:07-25

Please look at my screenshots and help me to understand what I am missing. What datatype should I choose for these columns in MYSQL? I keep getting mistakes in decimal datatype columns. I chose decimаl12,3 because no columns(revenue, product&purchase price) with currency have more than 12 digits in total, 9 before and 3 after the decimal point. Could someone help me to understand what data type to choose with examples?

  1. if we have an integer number e.g. 85192 we choose int?

  2. for currency we choose the decimal, right? then what have I done wrong that I keep getting errors? 0 records imported.

  3. if we have a combination of numbers and letters or just letters then we choose varchar? and varchаr1 equals 1 character, eg. apple32 = 7 characters, therefore vаrchar7?

  4. turning to decimal, 12,464.87 in total 7 digits, 5 before and 2 after the decimal point, hence mysql decimаl7,2 should be enough, right? or would it be better to put decimаl10,3 with a margin so to say.

    excel mysql data

CodePudding user response:

$1,000.00 contains two characters that cannot be part of a numeric literal: the dollar sign and the comma that is used as a thousands separator.

Find a way to change '$1,000.00' to '1000.00' in the input file. Then, the load will succeed.

Alternatively, create an intermediate table where product_price is a VARCHAR(32), load into that, and then:

INSERT INTO target_table 
SELECT 
 other_col1
,other_col2
, ....
,CAST(REPLACE(REPLACE(product_price,',',''),'$','') AS DECIMAL(15,2)
,other_col_n
,... 
FROM staging_table;
  • Related