trying to create a table in mysql where one column would have array as datatype

[Souranil21 chakraborty]

i am trying to make a table where one column would content an array but when creating the table i am facing this problem.I am pretty new to mysql so any help would be appreciated.

 

[mariushm]

I don't think mysql has an array data type ... and usually you don't use varchar for more than around 250 bytes/characters (depends on what text encoding you use it's a distinction, a row is limited to 64 KB on MyISAM tables (default mysql) or half the page size (8 KB is the default, can be configured higher) on the better InnoDB tables that also support transactions and are safer to use.

See MySQL :: MySQL 8.0 Reference Manual :: 8.4.7 Limits on Table Column Count and Row Size

 

So as for your question I would either use a separate table for the array entries and just store a unique ID pointing to the entries in the other table that have the array entries or you could set the column type as text and use some special encoding to encode the array as a text string  (for example you could serialize it using various functions, maybe json encode it) or you could have something basic like  2 bytes for the entry length followed by the entry text (up to entry length value), repeat until entry length is 0 or end of string.

[Eigenvektor]

A table is essentially an array of values. If you need a "sub-array" then what you really want is another table, that references the first one through a foreign key.

 

Something like this:

CREATE TABLE NotablePlace (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(40),
    city VARCHAR(30),
    content VARCHAR(500)
)

CREATE TABLE NotablePlaceMention (
    place_id INT,
    mention TEXT,
    CONSTRAINT `fk_place_mention` FOREIGN KEY (place_id) REFERENCES NotablePlace (id) ON DELETE CASCADE ON UPDATE RESTRICT
)

 

[Souranil21 chakraborty]

2 hours ago, Eigenvektor said:

A table is essentially an array of values. If you need a "sub-array" then what you really want is another table, that references the first one through a foreign key.

 

Something like this:

CREATE TABLE NotablePlace (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(40),
    city VARCHAR(30),
    content VARCHAR(500)
)

CREATE TABLE NotablePlaceMention (
    place_id INT,
    mention TEXT,
    CONSTRAINT `fk_place_mention` FOREIGN KEY (place_id) REFERENCES NotablePlace (id) ON DELETE CASCADE ON UPDATE RESTRICT
)

 

yes i am thinking of doing something similar of maintaining another table but i also found something about using json datatype for storing something like this. Was wondering if that can be a good choice. Also according to a article i found it said arrays can be a part of a table as column but why this error particularly if u could mention something.

[mariushm]

The error tells you that  mysql doesn't understand what you want to say with the brackets  "[]" after the keyword TEXT.

 

Mysql 8 seems to support JSON data type for columns, the documentation is here :  https://dev.mysql.com/doc/refman/8.0/en/json.html

 

I wouldn't use it, would rather use TEXT and use programming language functions to create the json string or decode the json string received from database.