Save fixes in database

2

I have arrangements that I want to save in the database, the arrays have around 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 arrangements and not element by element

in the bd I put

piel           varchar(1000) not null,

But if in case my skin arrangement has 2000 elements? varchar (1000) would no longer be enough, I need an unlimited size?

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

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

    
asked by x-rw 17.08.2018 в 19:50
source

1 answer

3

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

  • MySQL 5.7
  • MySQL 8
  • MariaDB 10.2
  • Important

      

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

    In this way

    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 CONSULT A SPECIFIC JSON VALUE

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

    TO CONSULT THE COMPLETE 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 command DESCRIBE person; to check more about the table you will notice that it internally 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 perhaps aesthetic purposes only

    We can use the JSON_DETAILED function of mariaDB, (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                                                             |
    |         |     }                                                                         |
    |         | }                                                                             |
    +---------+-------------------------------------------------------------------------------+
    
        
    answered by 17.08.2018 / 20:02
    source