VARCHAR类型经度字段返回科学计数法及LOAD DATA导入问题求助

Hey Jason, let's work through this longitude formatting issue step by step—this is a common gotcha with numeric imports in MySQL, so we'll get it sorted.

First, let's diagnose the core problem #

Your DECIMAL(15,7) field is more than sufficient to store valid longitude values (-180 to 180 only needs 3 integer digits + 7 decimal digits, which fits easily in the 15 total digits). The scientific notation display (-1.4964E+9) tells us MySQL isn't storing the value you expect—it's actually holding a massive number (-1,496,400,000) instead of -149.6436000. This almost always ties back to how LOAD DATA INFILE is parsing your tab-separated, quoted data.

Fix 1: Correct your LOAD DATA INFILE command #

The key missing piece here is telling MySQL to handle the double quotes wrapping some of your fields (including the quoted longitude values). Without this, MySQL treats the quotes as part of the value, which can break numeric conversion.

Use this adjusted command (swap in your table/field names and file path):

LOAD DATA INFILE '/path/to/your/datafile.txt'
INTO TABLE your_target_table
FIELDS TERMINATED BY '\t'  -- Confirms tab-separated fields
ENCLOSED BY '"'             -- Tells MySQL to strip wrapping quotes from ALL fields
LINES TERMINATED BY '\n'    -- Adjust if your file uses a different line ending (e.g., '\r\n' for Windows)
(city, state, zip, empty_col, latitude, longitude);  -- Match your table's column order exactly

Fix 2: Verify the imported value (don't trust client display alone) #

Some MySQL clients (like certain versions of Workbench) will auto-switch to scientific notation for very large/small numbers, but if our fix worked, the actual value should be correct. To confirm:

SELECT longitude, CAST(longitude AS CHAR) FROM your_target_table LIMIT 1;

If the CAST result shows -149.6436000, the value is stored correctly, and the scientific notation is just a client display quirk. If not, move to the next step.

Fix 3: Use a temp table to manually control conversion #

If automatic parsing still fails, import into a temporary VARCHAR table first, then cast to DECIMAL explicitly—this gives you more control over the conversion:

-- 1. Create a temp table with VARCHAR fields
CREATE TABLE temp_geo_data (
    city VARCHAR(100),
    state VARCHAR(2),
    zip VARCHAR(10),
    empty_col VARCHAR(5),
    latitude VARCHAR(15),
    longitude VARCHAR(15)
);

-- 2. Import data to temp table (same quoting/separator rules)
LOAD DATA INFILE '/path/to/your/datafile.txt'
INTO TABLE temp_geo_data
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 3. Insert into your main table with explicit casting
INSERT INTO your_target_table (city, state, zip, empty_col, latitude, longitude)
SELECT
    city,
    state,
    zip,
    empty_col,
    CAST(latitude AS DECIMAL(15,7)),
    CAST(longitude AS DECIMAL(15,7))
FROM temp_geo_data;

-- Clean up the temp table if needed
DROP TABLE temp_geo_data;

Quick sanity check #

Make sure your MySQL session is using the correct decimal separator (should be . for your data). Verify with:

SHOW VARIABLES LIKE 'decimal_point';

If it returns , instead, run SET SESSION decimal_point = '.'; before importing to avoid parsing decimals incorrectly.

内容的提问来源于stack exchange，提问作者Jason Cannon