Home / Questions / 189598
Accepted
userStack
Asked: 2020-08-18 09:50:13 +0800 CST 2020-08-18 09:50:13 +0800 CST

Save arrays to database

  • 772

I have arrays that I want to save in the database, the arrays have about 1000 elements, should I save them in string or in what type?

    +--------------------+ 
    tabla caracteristicas 
    piel [datos] 
    ojos [datos] 
    expresiones  [datos]
   +--------------------+

I must save the complete arrays and not element by element

in the bd I put

piel           varchar(1000) not null,

But what if my skin array has 2000 elements? varchar(1000) would no longer suffice, do I need unlimited size?

Note : Also, if I want to save 1000 elements with varchar(1000) it would not supply either, because since it is a string it counts up to the brackets, commas...

when I insert with json ( VALUES (1,'{"data": {"language": "spanish", "age": 39}}',1,1);) according to @Alfredo's answer I get the following:

enter image description here

mariadb

1 Answers

  1. Best Answer

    It depends, you can use the JSON data format, in the following cases

    1. MySQL 5.7
    2. MySQL 8
    3. MariaDB 10.2

    Important

    The syntax that I provide you is only entirely for mariaDB, in MySQL there are some variations regarding these JSON data query functions

    Thus

    MariaDB [blog]> CREATE TABLE persona(
    -> name VARCHAR(20) NOT NULL,
    -> attributes JSON NOT NULL);

    Although the format is JSON, internally it is treated as a LONG TEXT

    TO INSERT

    MariaDB [blog]> INSERT INTO persona(name, attributes)
    -> VALUES
    -> ('alfredo', '{"data": {"language": "spanish", "age": 39}}');

    TO QUERY A SPECIFIC JSON VALUE

    MariaDB [blog]> SELECT name, JSON_VALUE(attributes, '$.data.language') AS Data FROM persona;
+---------+---------+
| name    | Data    |
+---------+---------+
| alfredo | spanish |
+---------+---------+

    TO CHECK THE FULL JSON

    MariaDB [blog]> SELECT name, JSON_QUERY(attributes, '$.data') AS Data FROM persona;
+---------+------------------------------------+
| name    | Data                               |
+---------+------------------------------------+
| alfredo | {"language": "spanish", "age": 39} |
+---------+------------------------------------+

    MORE ABOUT JSON

    If you do the DESCRIBE person command; to check more about the table you will realize that internally it handles it as a LONG TEXT

    MariaDB [blog]> describe persona;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(20) | NO   |     | NULL    |       |
| attributes | longtext    | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

    UPDATE

    Tu estructura pudiera quedar así

MariaDB [blog]> INSERT INTO persona(name, attributes)
    -> VALUES
    -> ('beto', '{"valor1": 1, "valor2": 2}');
Query OK, 1 row affected (0.098 sec)

MariaDB [blog]> SELECT * FROM persona;
+---------+----------------------------------------------+
| name    | attributes                                   |
+---------+----------------------------------------------+
| alfredo | {"data": {"language": "spanish", "age": 39}} |
| beto    | {"valor1": 1, "valor2": 2}                   |
+---------+----------------------------------------------+

    For purposes perhaps only aesthetic

    We can use mariaDB's own JSON_DETAILED function (its equivalent in MySQL would be JSON_PRETTY) to get a better view of the structure

    MariaDB [blog]> SELECT name, JSON_DETAILED(attributes) FROM persona;

+---------+-------------------------------------------------------------------------------+
| name    | JSON_DETAILED(attributes)                                                     |
+---------+-------------------------------------------------------------------------------+
| alfredo | {                                                                             |
|         |     "data":                                                                   |
|         |     {                                                                         |
|         |         "language": "spanish",                                                |
|         |         "age": 39                                                             |
|         |     }                                                                         |
|         | }                                                                             |
+---------+-------------------------------------------------------------------------------+
    • 3