is it possible to create a document database in MySQL?

Question

is it possible to create a document database in MySQL?

Navigation

#1 by (1 votes)

0

Inside MySQL 5.7 onwards until version 8; we are already able to use attributes for data of type JSON

Like this:

CREATE TABLE profile(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) UNIQUE NOT NULL,
  attributes JSON NOT NULL
);

Later to do the insertion of data, we do the following

INSERT INTO profile(name, attributes)
VALUES
('alfred', '{"data": {"backend": true, "frontend": "maybe"}}')

Some functions like JSON_EXTRACT() allow me to read the data of type JSON in this way

SELECT name, JSON_EXTRACT(attributes, '$.data') AS Data FROM profile;

If for example we want to read the value of a specific key of our JSON structure we can do the following

The following syntax is the equivalent of JSON_EXTRACT has the same functionality but the structure is shortened; another detail is that for example

->> helps remove the quotes from the text strings that result from the values

-> shows the result but includes the quotation marks in the text string

SELECT name, attributes- > '$. data.backend' AS Data FROM profile;

However, the previous thing follows under the slogan of using SQL although the JSON_FUNCTIONS() allow to operate the data with greater flexibility

Here the source: link

But the question is this:

Can I create a documentary database as allowed by, for example, MongoDB?

mysql sql nosql

asked by element 23.09.2018 в 22:12

source

1 answer

Call to a member function random on null in Laravel 5.7 Test functions you create in Haskell

score 1 · Accepted Answer

Since version 8 of MySQL we can use the mysql 8 shell that is installed separately that allows us to use MySQL as a document store engine

(it is important to emphasize that only version 8 works and we must download that shell separately) for example in Windows with MySQL Installer help

Once the shell of mysql 8 is open, we connect as follows

\connect root@localhost/ejemplo

Where example, is the name of an empty database created in a traditional way

Now we need to create our first collection which we are going to call movies ; as follows

db.createCollection('movies')

db is a global variable that is assigned as a value the name of the chosen schema, in this case movies

Now to insert the first record we execute the following command

db.movies.add({ "name": "avengers 4", "cool": true })

Which should return a value like the following

Query OK, 1 item affected (0.3737 sec)

To read the newly saved record we do the following

db.movies.find()

That should show us something like the following

[
    {
        "_id": "00005ba7b33c0000000000000001",
        "name": "avengers 4",
        "year": 2018
    }
]
1 document in set (0.0012 sec)

If for example we want to modify the value of some key of the newly entered record we do the following

db.movies.modify("name = 'avengers 4'").set("cool", false)

Finally to delete a record permanently we make the following command

db.movies.remove("name = 'avengers 4'")

Finally, if we want to eliminate the collection called movies completely, just execute the following command

db.dropCollection('movies')

For example, from the command line we can do the following

show tables; (within the example database)

That will show us the following

+-------------------+
| Tables_in_ejemplo |
+-------------------+
| movies            |
+-------------------+

If I now do a SELECT * of the movies table, I will have a result similar to this

mysql> select * from movies;
+-----------------------------------------------------------------------+------------------------------+
| doc                                                                   | _id                          |
+-----------------------------------------------------------------------+------------------------------+
| {"_id": "00005ba7b33c0000000000000004", "age": 29, "name": "alfredo"} | 00005ba7b33c0000000000000004 |
| {"_id": "00005ba7b33c0000000000000005", "age": 35, "name": "danel"}   | 00005ba7b33c0000000000000005 |
+-----------------------------------------------------------------------+------------------------------+

Here is an official reference: link

I add information regarding the topic of indexes, their creation and elimination in the collections

link