Using MySQL X API as a NoSQL Alternative: Configuration, CRUD Examples, and Advanced Operations

Background: a user asks whether MySQL can handle flexible‑schema data without deploying a separate NoSQL system.

Answer: MySQL X API, introduced in MySQL 5.7, can serve as a NoSQL‑style interface. To enable it, add the X port to the MySQL configuration.

#my.cnf
[mysqld]
port=3305               # traditional MySQL port
mysqlx_port=33050          # MySQL X protocol port

Example – inserting ten documents into MongoDB and performing the same operation with MySQL X API.

# MongoDB shell
use ytt
var c1 = []
for (var i = 0; i < 10; i++) { c1.push({x:i, y:i*2, z:i+100}); }
db.f1.insert(c1);
print(db.f1.count()); // 10

# MySQL Shell (X protocol)
db.createCollection('f1');
var c1 = []
for (var i = 0; i < 10; i++) { c1.push({x:i, y:i*2, z:i+100}); }
db.f1.add(c1);
print(db.f1.count()); // 10

Further operations such as update, delete, array manipulation, indexing and transactions are demonstrated.

# Update a record where x=1
db.f1.modify('x=1').set('x','mysql');

# Delete all records
db.f1.remove('true');

# Append an array to field x
db.f1.modify('true').arrayAppend('$.x', c1);

# Create an index on field x
db.f1.createIndex('idx_x', {fields:[{'field':'$.x','type':'int'}]});

# Transaction example
session.startTransaction();
db.f1.remove('x=1');
session.rollback();

The examples demonstrate that MySQL X API provides full CRUD, indexing, and transaction capabilities, making it a practical substitute for MongoDB or other NoSQL solutions.