如何定位INSERT语句中触发‘Cannot convert’错误的列？

Got it, let's break down how to track down that pesky 'Cannot convert' error without manually checking 200 columns one by one. Here are practical approaches for both T-SQL and your MyBatis/Java setup:

Instead of writing 200 repetitive ISDATE/ISNUMERIC checks, we can generate dynamic SQL based on your table's schema to validate every column automatically. This uses TRY_CONVERT/TRY_CAST—way more reliable than ISNUMERIC, which false-positives on characters like $ or ,.

Here's a script that scans your table and returns all invalid columns, their bad values, and the full problematic row:

DECLARE @TargetTable NVARCHAR(128) = 'YourTableNameHere';
DECLARE @ValidationSQL NVARCHAR(MAX) = '';

-- Build validation logic for each target column
SELECT @ValidationSQL += '
SELECT 
    ''' + COLUMN_NAME + ''' AS InvalidColumn,
    ' + COLUMN_NAME + ' AS InvalidValue,
    *
FROM ' + @TargetTable + '
WHERE ' +
    CASE DATA_TYPE
        WHEN 'date' THEN 'TRY_CONVERT(date, ' + COLUMN_NAME + ') IS NULL'
        WHEN 'numeric' THEN 'TRY_CAST(' + COLUMN_NAME + ' AS NUMERIC) IS NULL'
        WHEN 'char' THEN -- Adjust based on your char column rules (e.g., max length)
            'LEN(RTRIM(' + COLUMN_NAME + ')) > ' + CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR)
    END + '
UNION ALL'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TargetTable 
  AND DATA_TYPE IN ('date', 'numeric', 'char');

-- Remove the trailing UNION ALL
SET @ValidationSQL = LEFT(@ValidationSQL, LEN(@ValidationSQL) - 10);

-- Execute the dynamic validation
EXEC sp_executesql @ValidationSQL;

Run this, and you'll get a clear list of exactly which columns have invalid data—no guessing required.

Since you can't use PreparedStatement, catching bad data before it reaches the database is the way to go. Here are two clean approaches:

1. Schema-Driven Pre-Insert Validation #

Use JDBC metadata to pull your table's column types, then validate each field in Java before sending it to MyBatis:

// 1. Fetch table schema metadata
Connection connection = getYourConnection(); // Get from your data source
DatabaseMetaData metaData = connection.getMetaData();
ResultSet columns = metaData.getColumns(null, null, "YourTableNameHere", null);

// Map column names to their data types
Map<String, String> columnTypeMap = new HashMap<>();
while (columns.next()) {
    columnTypeMap.put(
        columns.getString("COLUMN_NAME"),
        columns.getString("TYPE_NAME")
    );
}

// 2. Validate your data (example using a Map of column-value pairs)
Map<String, Object> insertData = getYourInsertData(); // Your data to insert
for (Map.Entry<String, Object> entry : insertData.entrySet()) {
    String colName = entry.getKey();
    Object value = entry.getValue();
    if (value == null) continue; // Skip nulls (adjust if your columns don't allow nulls)

    try {
        switch (columnTypeMap.get(colName).toLowerCase()) {
            case "date":
                // Validate date format (adjust pattern to match your DB's expected format)
                DateTimeFormatter.ofPattern("yyyy-MM-dd").parse(value.toString());
                break;
            case "numeric":
                // Validate numeric format
                new BigDecimal(value.toString());
                break;
            case "char":
                // Validate char length (pull max length from metadata)
                ResultSet colDetails = metaData.getColumns(null, null, "YourTableNameHere", colName);
                colDetails.next();
                int maxLength = colDetails.getInt("COLUMN_SIZE");
                if (value.toString().length() > maxLength) {
                    throw new IllegalArgumentException("Column " + colName + " exceeds max length: " + maxLength);
                }
                break;
        }
    } catch (Exception e) {
        throw new RuntimeException("Invalid data in column: " + colName + ", value: " + value, e);
    }
}

This will throw a clear error with the problematic column name before the insert even runs.

2. Custom MyBatis TypeHandlers #

Extend MyBatis's TypeHandler to catch conversion errors and tag the column name:

public class ValidatingDateTypeHandler extends BaseTypeHandler<String> {
    private final String columnName;

    // Inject column name via constructor (configure in MyBatis mapper)
    public ValidatingDateTypeHandler(String columnName) {
        this.columnName = columnName;
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        try {
            DateTimeFormatter.ofPattern("yyyy-MM-dd").parse(parameter);
            ps.setDate(i, Date.valueOf(parameter));
        } catch (DateTimeParseException e) {
            throw new SQLException("Invalid date format in column: " + columnName + ", value: " + parameter, e);
        }
    }

    // Implement other TypeHandler methods (getResult) as needed
}

Then reference this handler in your MyBatis mapper for date columns:

<insert id="insertData">
    INSERT INTO YourTable (date_col, numeric_col, char_col)
    VALUES (
        #{dateCol, typeHandler=com.yourpackage.ValidatingDateTypeHandler, constructorArgs=[date_col]},
        #{numericCol},
        #{charCol}
    )
</insert>

Repeat this pattern for numeric and char columns to get column-specific error messages.

• Use the T-SQL script to audit existing data in your table.
• Use the Java/MyBatis checks to prevent bad data from being inserted in the first place.

Both approaches eliminate the need for manual column-by-column checks and give you precise info about which column is causing the conversion error.

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