background

Description of the problem:

We are now fully MySQL in the background database and very familiar with MySQL in front-end development.But now there's new business coming in.The data model is very flexible and does not have a fixed table structure.At first we thought about MySQL's memcached API.But to be honest, it's very chicken ribs and can't be fully released.In this part, we have considered NoSQL databases, but if we want to deploy NoSQL databases, we have to go through a series of tests and validations.Is MySQL a good solution for this?

My Answer:

Yes, absolutely!The MySQL X API completely replaces the NoSQL database.And this functionality is available from MySQL 5.7.MySQL 5.7 has also been available for several years and can be used directly.

MySQL X

To use the MySQL X protocol, you must include the parameter mysqlx_port in the configuration file when MySQL starts

#my.cnf
[mysqld]
port=3305 --Primitive mysql port
mysqlx_port=33050　--mysql x Protocol Port

Compare mongoDB with MySQL, the most popular NoSQL database.Let's start with a simple example of inserting a table:

Insert 10 records in the mongoDB table f1 (field x y z),

# mongodb shell
> use ytt
> switched to db ytt

# Define a js array,
> var c1 = [] 
> for (var i = 0;i < 10;i++){
> ... c1.push({'x':i,'y':i*2,'z':i+100}) 
> ... }
> 10
 
# Insert the array just now
> db.f1.insert(c1);
> BulkWriteResult({
> "writeErrors" : [ ],
> "writeConcernErrors" : [ ],
> "nInserted" : 10,
> "nUpserted" : 0,
> "nMatched" : 0,
> "nModified" : 0,
> "nRemoved" : 0,
> "upserted" : [ ]
> })

# The number of records is now 10
> db.f1.count()
> 10

# Take out the first item
> db.f1.find().limit(1).pretty();
{
	"_id" : ObjectId("5e0066a54af3d32384342edd"),
	"x" : 0,
	"y" : 0,
	"z" : 100
}

The implementation of mongoDB in MySQL is also very simple.

# mysql-shell
# Create a table f1
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.createCollection('f1')
<Collection:f1>

# Still define the same array c1
MySQL  ytt-pc:33050+ ssl  ytt  JS > var c1 = []
MySQL  ytt-pc:33050+ ssl  ytt  JS > for (var i = 0;i<10;i++) { c1.push({'x':i,'y':i*2,'z':i+100})}
10

# Insert the record just now
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.add(c1)
Query OK, 10 items affected (0.0058 sec)	
Records: 10  Duplicates: 0  Warnings: 0

# View total rows
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.count();
10

# Take out a record
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find().limit(1);
{
    "x": 0,
    "y": 0,
    "z": 100,
    "_id": "00005e006018000000000000000b"
}
1 document in set (0.0003 sec)

That's a basic insert. Let's look at other operations, such as updating and deleting

# mysql-shell
# Update records with field x value of 1 to MySQL 

MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.modify('x=1').set('x','mysql')
Query OK, 1 item affected (0.0047 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# Retrieving records for x='mysql'
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find("x='mysql'")
{
    "x": "mysql",
    "y": 2,
    "z": 101,
    "_id": "00005e006018000000000000000c"
}
1 document in set (0.0006 sec)

# Update field y value of'dble'has no where filter condition, that is, update the full table
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.modify("true").set('x','dble')
Query OK, 10 items affected (0.0075 sec)	
Rows matched: 10  Changed: 10  Warnings: 0

MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find();
{
    "x": "dble",
    "y": 0,
    "z": 100,
    "_id": "00005e006018000000000000000b"
}
...
{
    "x": "dble",
    "y": 18,
    "z": 109,
    "_id": "00005e0060180000000000000014"
}
10 documents in set (0.0010 sec)

MySQL  ytt-pc:33050+ ssl  ytt  JS > 
 
# For example, what should I do if I want to embed the c1 array into field x now?
 
# Set x to an empty array
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.modify("true").set("x",[])
Query OK, 10 items affected (0.0048 sec)
Rows matched: 10  Changed: 10  Warnings: 0

# Update field x with array push method

 MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.modify("true").arrayAppend("$.x",c1);
Query OK, 10 items affected (0.0064 sec)

Rows matched: 10  Changed: 10  Warnings: 0

# View the results just updated (simplified display)
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find()
{
    "x": [
        [
            {"x": 0, "y": 0, "z": 100},
            { "x": 1,"y": 2,"z": 101},
            {"x": 2,"y": 4,"z": 102},
            {"x": 3,"y": 6,"z": 103},
            {"x": 4,"y": 8,"z": 104},
            {"x": 5,"y": 10,"z": 105},
            {"x": 6,"y": 12,"z": 106},
            {"x": 7,"y": 14,"z": 107},
            {"x": 8, "y": 16,"z": 108},
            {"x": 9,"y": 18,"z": 109}
        ]
    ],
    "y": 0,
    "z": 100,
    "_id": "00005e006018000000000000000b"
},
...	
10 document in set (0.0003 sec)

# Look at the Delete operation

MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.remove('true');
Query OK, 10 items affected (0.0032 sec)

# All data cleared
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find()
Empty set (0.0003 sec)

//That's the most important query.Queries are filtered by the find() method.See the example below.

#mysql-shell
# Reinsert 10W records
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.remove('true');
Query OK, 10 items affected (0.0043 sec)

MySQL  ytt-pc:33050+ ssl  ytt  JS > var c1 = []
MySQL  ytt-pc:33050+ ssl  ytt  JS > for (var i =0;i< 100000;i++){c1.push({'x':i,'y':2*i,'z':i+100})}
100000

MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.add(c1)
Query OK, 100000 items affected (2.5686 sec)
Records: 100000  Duplicates: 0  Warnings: 0

# Take out the records with filter condition x>100 and x < 200 and output the first two records in reverse order
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find('x >100 and x < 200').sort(' x desc').limit(2)
{
    "x": 199,
    "y": 398,
    "z": 299,
    "_id": "00005e00601800000000000000e6"
}
{
    "x": 198,
    "y": 396,
    "z": 298,
    "_id": "00005e00601800000000000000e5"
}
2 documents in set (0.0766 sec)

# Query time 0.0766 seconds

# It is also very convenient to index field x
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.createIndex('idx_x',{fields:[{'field':'$.x','type':'int'}]});
Query OK, 0 rows affected (0.2854 sec)

MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find('x >100 and x < 200').sort(' x desc').limit(2)
{
    "x": 199,
    "y": 398,
    "z": 299,
    "_id": "00005e00601800000000000000e6"
}
{
    "x": 198,
    "y": 396,
    "z": 298,
    "_id": "00005e00601800000000000000e5"
}
2 documents in set (0.0004 sec)
# Query time 0.0004 seconds


# It's easier to execute a block of things and use the session object instead
# Similar to start transaction statement
MySQL  ytt-pc:33050+ ssl  ytt  JS > session.startTransaction()
Query OK, 0 rows affected (0.0002 sec)

MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.remove('x=1')
Query OK, 1 item affected (0.0004 sec)
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find('x=1')
Empty set (0.0004 sec)

# rollback-like statement
MySQL  ytt-pc:33050+ ssl  ytt  JS > session.rollback()
Query OK, 0 rows affected (0.0014 sec)

# This record is still there
MySQL  ytt-pc:33050+ ssl  ytt  JS > db.f1.find('x=1')
{
    "x": 1,
    "y": 2,
    "z": 101,
    "_id": "00005e0060180000000000000020"
}
1 document in set (0.0004 sec)

summary

I've given a few classic examples to illustrate that MySQL can be used directly as a NoSQL database and has the functionality it should have.For example, add or delete, build indexes, transactions, and so on.It could even be a complete replacement for mongoDB.