Got it, let's work through this together. Since you're building an updated internal site, pulling data from SQL Server (auto-inserted by the VAX system), and need to transform that raw data into your target format before storing it in another MySQL 5.7.21 table—here’s a practical framework to start with.

First off, I’ll need the actual raw format example and target format example to craft a precise query tailored to your data structure. That said, I can walk you through the core concepts and common MySQL 5.7 functions you’ll use for this transformation.

Core Approach: INSERT...SELECT with String Manipulation #

The most efficient way to do this is using MySQL’s INSERT...SELECT syntax, which lets you transform data on the fly as you load it into your destination table. Here’s how it works:

1. First, get the SQL Server data into a MySQL staging table
   You’ll need to replicate the SQL Server data into a temporary/staging table in your MySQL instance. Options include:

   • Setting up a federated table (if your MySQL instance can connect directly to SQL Server)
   • Using a PHP script to pull data from SQL Server and insert it into the staging table
   • Using an ETL tool like mysqldump with cross-database connectivity

   Let’s call this staging table staging_vax_raw for our examples.

2. Transform and Load with INSERT...SELECT
   MySQL 5.7 has a solid set of string functions to handle most format conversions. Here are the ones you’ll likely rely on:

   • SUBSTRING_INDEX(): Split strings using a delimiter (perfect for delimited raw data)
   • CONCAT()/CONCAT_WS(): Combine multiple values or string segments into your target format
   • TRIM(): Strip extra spaces or unwanted leading/trailing characters
   • LEFT()/RIGHT(): Extract fixed-length segments from the start/end of a string
   • STR_TO_DATE()/DATE_FORMAT(): Convert date/time strings between formats

Example Skeleton Query

Suppose your raw data has a single column raw_payload with delimited values, and you need to split it into 3 columns in your destination table vax_processed_data:

INSERT INTO vax_processed_data (employee_id, vaccine_type, admin_date)
SELECT
  -- Extract employee ID from the first segment of raw_payload (delimited by ';')
  TRIM(SUBSTRING_INDEX(raw_payload, ';', 1)),
  -- Extract vaccine type from the second segment
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(raw_payload, ';', 2), ';', -1)),
  -- Convert raw date string (e.g., '20240520') to MySQL date format
  STR_TO_DATE(TRIM(SUBSTRING_INDEX(raw_payload, ';', -1)), '%Y%m%d')
FROM staging_vax_raw;

Critical Notes for MySQL 5.7 #

• No REGEXP_SUBSTR() support: Unlike MySQL 8.0+, 5.7 doesn’t have this function for regex-based extraction. Stick to delimiter/position-based functions like SUBSTRING_INDEX() unless you want to set up user-defined functions (UDFs)—which I’d avoid for simplicity.
• Fixed-width raw data? Use SUBSTRING(raw_column, start_position, length) to pull specific segments. For example, if employee IDs are always the first 6 characters: SUBSTRING(raw_payload, 1, 6).
• Test first: Always run the SELECT part of the query alone on a small sample of data to verify the transformation matches your target format before running the full INSERT.

Once you share your actual raw and target format examples, I can refine this into a query that fits your exact needs!

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