MySQL 9.7 Explored: Vector Search, JavaScript Stored Procedures & Bulk Import Gains

MySQL Background and the AI-Era Challenge

MySQL, born in 1995 and now owned by Oracle, remains the world's most widely used open-source relational database. Its popularity stems from quick setup, a thick ecosystem of drivers and ORMs for Java, Go, Python, and PHP, and a battle-tested InnoDB engine with solid transaction, MVCC, and crash-recovery support.

However, the rise of AI applications has exposed a gap: business data lives in MySQL while vector embeddings require a separate vector database, adding synchronization logic and consistency headaches. The MySQL 9 series aims to solve these "new-era problems" by bringing vector capabilities inside the relational engine.

Two Release Channels: LTS vs Innovation

MySQL now operates two release tracks:

LTS (Long-Term Support) — e.g., 8.4: stability-first, bug fixes only, suited for long-running production workloads.

Innovation — 9.0, 9.1 … 9.7: new features every few months, shorter maintenance windows (each minor version stops receiving patches once the next one ships), ideal for teams wanting early access.

Therefore, 9.7 is not simply "one major version above 8.4"; it is the latest episode in the Innovation channel. Choose LTS for stability, Innovation for new capabilities — don't obsess over version numbers.

Four Headline Features in the 9 Series

3.1 VECTOR Type: Native Semantic Understanding

Starting with 9.0, MySQL introduced a native VECTOR data type, refined through 9.7. A vector is a floating-point array (e.g., 768 dimensions) produced by an embedding model that captures semantic meaning; similar meanings yield nearby vectors, turning search into a nearest-neighbor problem.

Previously, vectors were stuffed into JSON or BLOB columns and distance calculations were done in the application layer — a approach that collapses at scale. Now you can define a table with a vector column alongside business fields:

-- Create database with utf8mb4
CREATE DATABASE db_ai_search DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE db_ai_search;

-- Document table: business fields and vector column in the same table
CREATE TABLE t_doc (
    id          BIGINT      NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
    title       VARCHAR(200) NOT NULL                COMMENT 'Document title',
    content     TEXT                                COMMENT 'Document body',
    embedding   VECTOR(768)                         COMMENT 'Body embedding, 768-dim',
    create_time DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    PRIMARY KEY (id)
) ENGINE=InnoDB COMMENT='Document table';

The dimension inside VECTOR(768) must match your embedding model (OpenAI text-embedding-3-small = 1536, BGE-base = 768). Insert vectors as strings and convert with STRING_TO_VECTOR:

-- Insert a document (vector values are illustrative; real vectors come from the model)
INSERT INTO t_doc (title, content, embedding)
VALUES (
    'MySQL Index Optimization Primer',
    'This article introduces B+ tree index structure and how covering indexes reduce table lookups...',
    STRING_TO_VECTOR('[0.0123, -0.0456, 0.0789, ... ]')
);

-- Verify stored vector dimension
SELECT id, title, VECTOR_DIM(embedding) AS dim, create_time
FROM t_doc
WHERE id = 1;

Semantic search becomes a single SQL statement that combines relational filtering with vector distance ordering:

-- Semantic search: find 5 documents closest to the user's question vector
SET @query_vec = STRING_TO_VECTOR('[0.0110, -0.0430, 0.0820, ... ]');

SELECT
    id,
    title,
    DISTANCE(embedding, @query_vec, 'COSINE') AS score,
    DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS create_time_str
FROM t_doc
ORDER BY score ASC  -- smaller distance = closer meaning
LIMIT 5;

Key benefit: no separate vector database; business data and vectors share the same transaction, and hybrid queries like "semantic search only in last three months' technical docs" are a simple WHERE clause — a scenario where standalone vector stores struggle.

Note: vector functions (especially distance calculation and vector indexes) differ between Community and Enterprise editions and across minor versions; always verify against the Release Notes of your exact version before production use.

3.2 JavaScript Stored Programs: Logic Moves Next to Data

Traditional MySQL stored procedures use an archaic SQL dialect that makes JSON parsing painful. MySQL 9 allows writing stored functions and procedures in JavaScript , with reusable libraries via CREATE LIBRARY.

The real win is eliminating data-shipping overhead. Example: an orders table stores order details in a JSON column; you need a weighted discount per order. The old way pulls every row to the application, computes, and writes back — 100k rows = 100k round-trips. With a JS stored function the computation happens beside the data:

-- JavaScript function to calculate order discount
CREATE FUNCTION fn_calc_discount(order_detail JSON)
RETURNS DECIMAL(10,2)
LANGUAGE JAVASCRIPT
AS $$
    // Parse order details, apply per-category discounts
    const items = JSON.parse(order_detail);
    let total = 0;

    for (const item of items) {
        const amount = item.price * item.qty;
        // Books 20% off, digital 5% off, else full price
        if (item.category === 'book') {
            total += amount * 0.8;
        } else if (item.category === 'digital') {
            total += amount * 0.95;
        } else {
            total += amount;
        }
    }
    return Math.round(total * 100) / 100;
$$;

Usage is identical to a built-in function:

SELECT
    order_no,
    fn_calc_discount(detail_json) AS pay_amount,
    DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS create_time_str
FROM t_order
WHERE create_time >= '2026-09-01';

One SQL replaces N network round-trips. Caveats: reserve this for "data-intensive, stable logic"; frequently changing business rules belong in the application layer. Also, JavaScript stored programs are an Enterprise Edition feature — Community Edition users cannot use them.

3.3 Bulk Import Acceleration: No More All-Nighters

Data migration of hundreds of millions of rows has always been grueling. The 9 series delivers three concrete improvements:

Direct loading from URL / object storage (e.g., S3) — no need to download files locally before LOAD DATA.

Parallel loading — multiple threads stream data into InnoDB simultaneously.

Reduced index-maintenance and logging overhead for bulk inserts into empty tables.

Syntax example (adjust to actual version documentation):

-- Bulk import directly from object storage
LOAD DATA FROM URL 's3://my-bucket/data/user_2026.csv'
INTO TABLE t_user
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'
IGNORE 1 LINES
(user_name, nick_name, gender, create_time);

In ten-million-row tests this combination cuts import time dramatically — exact figures depend on disk, network, and schema, but the direction is clear: offline data loading is far less painful.

3.4 Security & Observability: Dropping Legacy Baggage

Removal of mysql_native_password . This legacy authentication plugin is gone from 9.0 onward; the default is now caching_sha2_password. Security improves, but older drivers (JDBC, PHP) may fail to connect — audit client versions before upgrading.

-- Check for accounts still using non-default plugins
SELECT user, host, plugin
FROM mysql.user
WHERE plugin <> 'caching_sha2_password';

-- Create account with explicit modern plugin (test password only; use strong passwords in prod)
CREATE USER 'app_user'@'%'
    IDENTIFIED WITH caching_sha2_password BY '123456';
GRANT SELECT, INSERT, UPDATE, DELETE ON db_ai_search.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

EXPLAIN ANALYZE output can now be captured into a variable. Previously you had to scrape client output; now you can store the JSON plan directly for automated performance auditing:

-- Store execution plan as JSON in a variable for later archival
EXPLAIN ANALYZE FORMAT = JSON
INTO @plan
SELECT id, title FROM t_doc WHERE create_time >= '2026-09-01';

-- Extract cost info or write to a slow-query analysis table
SELECT JSON_EXTRACT(@plan, '$.query_block.cost_info') AS cost_info;

Hands-On: Building a Semantic Search with MySQL 9.7

End-to-end flow: user enters a natural-language query → application converts it to a vector via an embedding model → MySQL performs filtered nearest-neighbor search in a single statement.

Java service snippet (Spring JdbcTemplate, no Lombok):

package com.example.search.service;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;

/**
 * Document semantic search service
 * Converts user question to vector, delegates nearest-neighbor search to MySQL
 */
@Service
public class DocSearchService {
    private final JdbcTemplate jdbcTemplate;
    private final EmbeddingClient embeddingClient;

    public DocSearchService(JdbcTemplate jdbcTemplate, EmbeddingClient embeddingClient) {
        this.jdbcTemplate = jdbcTemplate;
        this.embeddingClient = embeddingClient;
    }

    /**
     * Search documents by semantic similarity
     * @param question user's natural-language question
     * @param topN number of results to return
     * @return list of documents with title, similarity score, formatted creation time
     */
    public List<Map<String, Object>> searchBySemantic(String question, int topN) {
        // 1. Convert question to vector string like [0.01, -0.02, ...]
        String queryVector = embeddingClient.embedToString(question);

        // 2. Single SQL: filter + vector distance ordering
        String sql = "SELECT id, title, "
                + "     DISTANCE(embedding, STRING_TO_VECTOR(?), 'COSINE') AS score, "
                + "     DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS createTimeStr "
                + "  FROM t_doc "
                + " WHERE embedding IS NOT NULL "
                + " ORDER BY score ASC "
                + " LIMIT ?";

        return jdbcTemplate.queryForList(sql, queryVector, topN);
    }
}

The critical point: WHERE and ORDER BY live in the same SQL. Adding "only last three months" or "only category X" is a simple WHERE predicate — no post-filtering in the application. This is the most direct payoff of embedding vector search inside a relational database.

Should You Upgrade? Decision Checklist

New features are tempting, but upgrades require caution. Practical advice:

Don't jump versions directly. From 5.7 to 9.x spans too many changes; step through 8.0/8.4 first.

Authentication plugin is the biggest trap. Legacy JDBC/PHP clients fail on caching_sha2_password — load-test thoroughly beforehand.

Innovation releases have a maintenance window. When 9.8 arrives, 9.7 stops receiving patches; your team must be ready to track versions.

Validate on a read replica first. Attach the new version as a replica, run real traffic for a while, observe compatibility and performance before promoting to primary.