MySQL for Automotive and Embedded Engineering

Concept

Automotive and embedded teams use MySQL for engineering data management: vehicle and ECU inventories, software release and flashing history, diagnostic trouble codes (DTCs), CAN database definitions (like DBC files), captured bus traffic summaries, and HIL/vehicle test results. MySQL's strengths here: easy deployment in labs, JSON for configurations, partitioning for time-series logs, LOAD DATA for fast ingestion, and replication to share data between labs and offices.

Domain primer:

17-character Vehicle Identification Number Electronic Control Unit (VCU, BMS, BCM, ECM, ADAS...)

Diagnostic Trouble Code (P = powertrain, C = chassis, B = body, U = network) CAN message / signal

Frame with an ID and payload; signals are bit fields scaled by factor and offset Hardware-in-the-Loop test bench

Why It Matters

Engineers answer daily questions with SQL: "Which vehicles still run old BMS software?", "Which DTCs appeared after the last flash?", "Which test cases fail on the newest release?", "Which CAN messages lose frames?".

Syntax

MySQL 8 syntax with window functions, JSON, partitioning, and LOAD DATA.

Example

36.2 Sample data 36.3 Vehicle lookup with ECU list (GROUP BY v.vehicle_id allows selecting v.vin and v.model because they are functionally dependent on the primary key.) 36.4 Outdated ECU software 36.5 DTC reporting Hours to clear:

Software running at the time each DTC was detected (point-in-time lookup with LATERAL):

36.6 CAN message statistics Note: frames_expected - frames_received on UNSIGNED columns would raise an out-of-range error if received ever exceeded expected; CAST(... AS SIGNED) protects against that (or set NO_UNSIGNED_SUBTRACTION).

Signal definitions with physical range:

(DOUBLE arithmetic may display values such as 3276.7000000000003; wrap in ROUND(..., 3) for reports.) 36.7 Test result analysis 36.8 High-volume CAN log ingestion

The primary key (vehicle_id, captured_at, can_id) clusters each vehicle's frames in time order, which makes time-window queries per vehicle very efficient; the partition key captured_at is part of the PK as required. (If a logger can record two frames with the same ID at the same microsecond, add a sequence column to the key.) For multi-terabyte raw logs, many teams keep raw frames in files (ASAM MDF4/BLF) or a time-series/columnar store and load only summaries and events into MySQL.

CREATE DATABASE IF NOT EXISTS vehicle_test CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE vehicle_test;

CREATE TABLE vehicle (
    vehicle_id  INT UNSIGNED NOT NULL PRIMARY KEY,
    vin         CHAR(17)     NOT NULL UNIQUE,
    model       VARCHAR(40)  NOT NULL,
    model_year  YEAR         NOT NULL,
    market      CHAR(2)      NOT NULL
);

CREATE TABLE ecu (
    ecu_id      INT UNSIGNED NOT NULL PRIMARY KEY,
    vehicle_id  INT UNSIGNED NOT NULL,
    ecu_code    VARCHAR(10)  NOT NULL,
    serial_no   VARCHAR(30)  NOT NULL UNIQUE,
    UNIQUE KEY uq_ecu_vehicle_code (vehicle_id, ecu_code),
    CONSTRAINT fk_ecu_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicle (vehicle_id)
);

CREATE TABLE software_version (
    sw_version_id  INT UNSIGNED NOT NULL PRIMARY KEY,
    ecu_code       VARCHAR(10)  NOT NULL,
    version_label  VARCHAR(20)  NOT NULL,
    release_date   DATE         NOT NULL,
    release_status ENUM('RELEASED', 'BETA', 'WITHDRAWN') NOT NULL,
    UNIQUE KEY uq_sw (ecu_code, version_label)
);

CREATE TABLE ecu_software (
    install_id     INT UNSIGNED NOT NULL PRIMARY KEY,
    ecu_id         INT UNSIGNED NOT NULL,
    sw_version_id  INT UNSIGNED NOT NULL,
    inst
…

INSERT INTO vehicle VALUES
(1, 'EHBAUREV250000001', 'Aurora EV',  2025, 'IN'),
(2, 'EHBAUREV250000002', 'Aurora EV',  2025, 'IN'),
(3, 'EHBTITAN260000003', 'Titan SUV',  2026, 'EU'),
(4, 'EHBAURIC250000004', 'Aurora ICE', 2025, 'IN');

INSERT INTO ecu VALUES
(1, 1, 'VCU', 'VCU-A-0001'), (2, 1, 'BMS', 'BMS-B-0001'), (3, 1, 'BCM', 'BCM-C-0001'),
(4, 2, 'VCU', 'VCU-A-0002'), (5, 2, 'BMS', 'BMS-B-0002'), (6, 2, 'BCM', 'BCM-C-0002'),
(7, 3, 'ECM', 'ECM-D-0003'), (8, 3, 'BCM', 'BCM-C-0003'), (9, 3, 'ADAS', 'ADS-E-0003'),
(10, 4, 'ECM', 'ECM-D-0004'), (11, 4, 'BCM', 'BCM-C-0004');

INSERT INTO software_version VALUES
(1, 'VCU', '1.0.0', '2025-01-15', 'RELEASED'), (2, 'VCU', '1.1.0', '2025-06-01', 'RELEASED'),
(3, 'BMS', '2.3.1', '2025-02-01', 'RELEASED'), (4, 'BMS', '2.4.0', '2025-07-10', 'RELEASED'),
(5, 'BCM', '5.0.2', '2024-12-01', 'RELEASED'), (6, 'ECM', '3.2.0', '2025-05-20', 'RELEASED'),
(7, 'ADAS', '0.9.5', '2025-10-01', 'BETA');

INSERT INTO ecu_software VALUES
(1, 1, 1, '2025-02-12 09:00:00'), (2, 1, 2, '2025-06-20 14:30:00'),
(3, 2, 3, '2025-02-12 09:10:00'), (4, 2, 4, '2025-07-15 11:00:00'),
(5, 3, 5, '2025-02-12 09:20:00'), (6, 4, 1, '2025-03-07 10:00:00'),
(7, 5, 3, '2025-03-
…

SELECT v.vin, v.model, v.model_year,
       GROUP_CONCAT(e.ecu_code ORDER BY e.ecu_code SEPARATOR ', ') AS ecus
FROM vehicle v
JOIN ecu e ON e.vehicle_id = v.vehicle_id
WHERE v.vin = 'EHBTITAN260000003'
GROUP BY v.vehicle_id;

+-------------------+-----------+------------+----------------+
| vin               | model     | model_year | ecus           |
+-------------------+-----------+------------+----------------+
| EHBTITAN260000003 | Titan SUV |       2026 | ADAS, BCM, ECM |
+-------------------+-----------+------------+----------------+

WITH current_sw AS (
    SELECT ecu_id, sw_version_id,
           ROW_NUMBER() OVER (PARTITION BY ecu_id ORDER BY installed_at DESC) AS rn
    FROM ecu_software
),
latest_release AS (
    SELECT ecu_code, sw_version_id, version_label,
           ROW_NUMBER() OVER (PARTITION BY ecu_code ORDER BY release_date DESC) AS rn
    FROM software_version
    WHERE release_status = 'RELEASED'
)
SELECT v.vin, e.ecu_code, cur.version_label AS installed, lr.version_label AS latest
FROM current_sw cs
JOIN ecu e                ON e.ecu_id = cs.ecu_id
JOIN vehicle v            ON v.vehicle_id = e.vehicle_id
JOIN software_version cur ON cur.sw_version_id = cs.sw_version_id
JOIN latest_release lr    ON lr.ecu_code = e.ecu_code AND lr.rn = 1
WHERE cs.rn = 1 AND cs.sw_version_id <> lr.sw_version_id
ORDER BY v.vin, e.ecu_code;

+-------------------+----------+-----------+--------+
| vin               | ecu_code | installed | latest |
+-------------------+----------+-----------+--------+
| EHBAUREV250000002 | BMS      | 2.3.1     | 2.4.0  |
| EHBAUREV250000002 | VCU      | 1.0.0     | 1.1.0  |
+-------------------+----------+-----------+--------+

SELECT d.dtc_code, c.description, c.severity,
       COUNT(*)                     AS occurrences,
       COUNT(DISTINCT d.vehicle_id) AS vehicles,
       SUM(d.status = 'ACTIVE')     AS active_now
FROM diagnostic_dtc d
JOIN dtc_catalog c ON c.dtc_code = d.dtc_code
GROUP BY d.dtc_code, c.description, c.severity
ORDER BY occurrences DESC, d.dtc_code;

+----------+---------------------------------+----------+-------------+----------+------------+
| dtc_code | description                     | severity | occurrences | vehicles | active_now |
+----------+---------------------------------+----------+-------------+----------+------------+
| B1234    | Door ajar switch circuit        | LOW      |           2 |        2 |          1 |
| P0301    | Cylinder 1 misfire detected     | HIGH     |           2 |        2 |          1 |
| P0A80    | Replace hybrid/EV battery pack  | HIGH     |           2 |        2 |          2 |
| C1A00    | Radar sensor misalignment       | MEDIUM   |           1 |        1 |          1 |
| U0100    | Lost communication with ECM/PCM | MEDIUM   |           1 |        1 |          0 |
+----------+---------------------------------+----------+-------------+----------+------------+

SELECT dtc_event_id, dtc_code,
       ROUND(TIMESTAMPDIFF(MINUTE, detected_at, cleared_at) / 60, 1) AS hours_to_clear
FROM diagnostic_dtc WHERE status = 'CLEARED' ORDER BY dtc_event_id;
-- 2: B1234 3.0 | 4: U0100 18.7 | 6: P0301 26.3

SELECT d.dtc_event_id, d.dtc_code, e.ecu_code, sw.version_label AS sw_at_detection
FROM diagnostic_dtc d
JOIN ecu e ON e.ecu_id = d.ecu_id
JOIN LATERAL (
    SELECT s.version_label
    FROM ecu_software es JOIN software_version s ON s.sw_version_id = es.sw_version_id
    WHERE es.ecu_id = d.ecu_id AND es.installed_at <= d.detected_at
    ORDER BY es.installed_at DESC
    LIMIT 1
) AS sw ON TRUE
ORDER BY d.dtc_event_id;
-- 1 BMS 2.4.0, 2 BCM 5.0.2, 3 BMS 2.3.1, 4 VCU 1.0.0, 5 ADAS 0.9.5, 6 ECM 3.2.0, 7 ECM 3.2.0, 8 BCM 5.0.2

Expected Result

The outdated-software query returns the two ECUs of vehicle EHBAUREV250000002; the CAN statistics flag BMS_PackStatus and VCU_Status for investigation.

Common Mistakes

Using offset, signal, range as column names (reserved words in MySQL).

Sorting version strings as text ('1.10.0' < '1.9.0'); sort by release date or store numeric parts.

Joining DTCs to *current* software instead of software at detection time.

Unsigned subtraction errors in frame-loss arithmetic.

Row-by-row network inserts from loggers.

Best Practices

Keep installation history; derive "current" with window functions or a view.

Use a DTC catalog table with FKs.

Partition time-series tables and automate retention with events.

Bulk-load with LOAD DATA; buffer on devices.

Interview Relevance

High for automotive/embedded roles: outdated-software queries, DTC trend reports, test pass rates per version, CAN frame loss, ingestion design.

Practice Questions

List vehicles with at least one HIGH-severity active DTC.

Count ECUs per software version for each ECU code.

Find test cases that have never passed.

Find DTCs detected within 7 days after a software installation on the same ECU.

Create a view v_ecu_current_software.