Type Safety and Data Conversion#

Gold Digger handles MySQL data types safely without panicking on NULL values or type mismatches.

Safe Type Handling#

Automatic Type Conversion#

Gold Digger automatically converts all MySQL data types to string representations:

• NULL values → Empty strings ("")
• Integers → String representation (42 → "42")
• Floats/Doubles → String representation (3.14 → "3.14")
• Dates/Times → ISO format strings (2023-12-25 14:30:45.123456)
• Binary data → UTF-8 string or hex encoding (0x...) for invalid UTF-8

Special Value Handling#

Gold Digger handles special floating-point values:

• NaN → "NaN"
• Positive Infinity → "Infinity"
• Negative Infinity → "-Infinity"

JSON Output Type Preservation#

When outputting to JSON format, Gold Digger preserves native MySQL types:

{
  "data": [
    {
      "id": 123, // Integer preserved as JSON number
      "price": 19.99, // Float preserved as JSON number
      "name": "Product", // String preserved
      "active": 1, // Boolean-like TINYINT(1) preserved as JSON number (0/1)
      "description": null // NULL preserved as JSON null
    }
  ]
}

Dates and times are formatted as ISO-8601 strings in JSON output, with T separator for datetimes (2023-12-25T14:30:45.123456).

Common Type Issues#

NULL Value Handling#

Problem: Database contains NULL values

Solution: Gold Digger handles NULLs automatically:

• CSV/TSV: NULL becomes empty string
• JSON: NULL becomes null value

-- This query works safely with NULLs
SELECT id, name, description FROM products WHERE id <= 10;

Mixed Data Types#

Problem: Column contains mixed data types

Solution: All values are converted to strings safely:

-- This works even if 'value' column has mixed types
SELECT id, value FROM mixed_data_table;

Binary Data#

Problem: Column contains binary data (BLOB, BINARY)

Solution: Binary data is converted to UTF-8 if valid, otherwise hex-encoded:

-- Binary columns are handled safely
SELECT id, binary_data FROM files;

Invalid UTF-8 bytes are encoded as 0x<hexstring> (e.g., 0xfffefd) to prevent data corruption. Large binary data (>1024 bytes) is truncated with indication: 0x<prefix>... (N bytes).

Date and Time Formats#

Problem: Need consistent date formatting

Solution: Gold Digger uses ISO format for all date/time values:

-- Date/time columns are formatted consistently
SELECT created_at, updated_at FROM events;

Output format:

• Date only: 2023-12-25
• DateTime: 2023-12-25 14:30:45.123456
• Time only: 14:30:45.123456

Best Practices#

Query Writing#

1. No casting required: Unlike previous versions, you don't need to cast columns to CHAR
2. Use appropriate data types: Let MySQL handle the data types naturally
3. Handle NULLs in SQL if needed: Use COALESCE() or IFNULL() for custom NULL handling

-- Good: Let Gold Digger handle type conversion
SELECT id, name, price, created_at FROM products;

-- Also good: Custom NULL handling in SQL
SELECT id, COALESCE(name, 'Unknown') as name FROM products;

Output Format Selection#

Choose the appropriate output format based on your needs:

• CSV: Best for spreadsheet import, preserves all data as strings
• JSON: Best for APIs, preserves data types where possible
• TSV: Best for tab-delimited processing, similar to CSV

Error Prevention#

Gold Digger's safe type handling prevents common errors:

• No panics on NULL values
• No crashes on type mismatches
• Graceful handling of special values (NaN, Infinity)
• Safe binary data conversion

Migration from Previous Versions#

Removing CAST Statements#

If you have queries with explicit casting from previous versions:

-- Old approach (still works but unnecessary)
SELECT CAST(id AS CHAR) as id, CAST(name AS CHAR) as name FROM users;

-- New approach (recommended)
SELECT id, name FROM users;

Handling Type-Specific Requirements#

If you need specific type handling, use SQL functions:

-- Format numbers with specific precision
SELECT id, ROUND(price, 2) as price FROM products;

-- Format dates in specific format
SELECT id, DATE_FORMAT(created_at, '%Y-%m-%d') as created_date FROM events;

-- Handle NULLs with custom values
SELECT id, COALESCE(description, 'No description') as description FROM items;

Troubleshooting Type Issues#

Unexpected Output Format#

Issue: Numbers appearing as strings in JSON

Cause: Value contains non-numeric characters or formatting

Solution: Clean the data in SQL:

SELECT id, CAST(TRIM(price_string) AS DECIMAL(10,2)) as price FROM products;

Binary Data Display Issues#

Issue: Binary data showing as garbled text

Cause: Binary column being converted to string

Solution: Gold Digger automatically hex-encodes invalid UTF-8. For custom formats, use SQL functions:

-- Convert binary to hex representation
SELECT id, HEX(binary_data) as binary_hex FROM files;

-- Or encode as base64 (MySQL 5.6+)
SELECT id, TO_BASE64(binary_data) as binary_b64 FROM files;

Date Format Consistency#

Issue: Need different date format

Solution: Format dates in SQL:

-- US format
SELECT id, DATE_FORMAT(created_at, '%m/%d/%Y') as created_date FROM events;

-- European format
SELECT id, DATE_FORMAT(created_at, '%d.%m.%Y') as created_date FROM events;

Performance Considerations#

Gold Digger's type conversion uses the TypeTransformer API for safety and performance:

• Zero-copy string conversion where possible
• Efficient NULL handling without allocations
• Streaming-friendly design for large result sets
• Deterministic hex encoding for binary data

The TypeTransformer implementation in src/type_transformer.rs provides the canonical safe MySQL value conversion, preventing crashes and data corruption with minimal overhead.

TypeTransformer API Reference#

Developers extending Gold Digger should use the TypeTransformer API:

use std::collections::BTreeMap;
use gold_digger::TypeTransformer;

// Convert single value to string (CSV/TSV):
let s: String = TypeTransformer::value_to_string(&value)?;

// Convert single value to JSON:
let json_value: serde_json::Value = TypeTransformer::value_to_json(&value)?;

// Convert full row to Vec<String>:
let strings: Vec<String> = TypeTransformer::row_to_strings(row)?;

// Convert full row to JSON map (deterministic ordering):
let map: BTreeMap<String, serde_json::Value> = TypeTransformer::row_to_json(row)?;

See src/type_transformer.rs for implementation details and safety guarantees.