Does mysql have arrays?

There are no array types in MySQL. Array elements are usually divided by a certain character and stored in string form. The reason there are no arrays in MYSQL is because most people don't really need it. In relational databases, typically using a relational model, each unit of information is best assigned to a separate table row. For example, one might think "I want a list of things" and instead create a new table that relates rows from one table to rows from another table; this can represent an "M:N" relationship. The database can index these rows; arrays typically are not indexed.

1. MySQL stores arrays in the form of strings

There are no array types in MySQL. Array elements are usually divided into strings by a certain character. Format storage

1.1. Find the number of elements in the array

Method: Split the string according to the specified symbol and return the number of elements after splitting. The required result can be obtained by counting the number of delimiters in the string and adding 1. The method is very simple.

<code>CREATE function Get_StrArrayLength <br/>( <br/>@str varchar(1024), --要分割的字符串 <br/>@split varchar(10) --分隔符号 <br/>) <br/>returns int <br/>as <br/>begin <br/>declare @location int <br/>declare @start int <br/>declare @length int <br/>set @str=ltrim(rtrim(@str)) <br/>set @location=charindex(@split,@str) <br/>set @length=1 <br/>while @location<>0 <br/>begin <br/>set @start=@location+1 <br/>set @location=charindex(@split,@str,@start) <br/>set @length=@length+1 <br/>end <br/>return @length <br/>end<br/></code>

Calling example:

select  Get_StrArrayLength('78,1,2,3',',')

Return value:

4

1.2. Get the element at the specified position in the array

Method: Split the string according to the specified symbol and return the element of the specified index after splitting (note that the index starts from 1), as convenient as an array

<code>CREATE function Get_StrArrayStrOfIndex <br/>( <br/>@str varchar(1024), --要分割的字符串 <br/>@split varchar(10), --分隔符号 <br/>@index int --取第几个元素 <br/>) <br/>returns varchar(1024) <br/>as <br/>begin <br/>declare @location int <br/>declare @start int <br/>declare @next int <br/>declare @seed int <br/>set @str=ltrim(rtrim(@str)) <br/>set @start=1 <br/>set @next=1 <br/>set @seed=len(@split) <br/>set @location=charindex(@split,@str) <br/>while @location<>0 and @index>@next <br/>begin <br/>set @start=@location+@seed <br/>set @location=charindex(@split,@str,@start) <br/>set @next=@next+1 <br/>end <br/>if @location =0 select @location =len(@str)+1 <br/>--这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。 <br/>return substring(@str,@start,@location-@start) <br/>end<br/></code>

Call example:

select  Get_StrArrayStrOfIndex('8,9,4',',',2)

Return value:

9

1.3. Combine the above two functions to traverse the elements in the array

Method: Combine the above two functions to traverse the elements in the string like an array

<code>declare @str varchar(50) <br/>set @str=&#39;1,2,3,4,5&#39; <br/>declare @next int <br/>set @next=1 <br/>while @next<=Get_StrArrayLength(@str,&#39;,&#39;) <br/>begin <br/>print Get_StrArrayStrOfIndex(@str,&#39;,&#39;,@next) <br/>set @next=@next+1 <br/>end<br/></code>

Call result:

1
2
3
4
5

2. Example of storing array (list) in MySQL

I have two tables in MySQL. Table Person has the following columns:

id | name | fruits

The fruit column can contain empty or an array of strings like (‘apple’, ‘orange’, ‘banana’) or (‘strawberry’) etc. The second table is Table Fruit, with the following three columns:

____________________________
fruit_name | color  | price
____________________________
apple      | red    | 2
____________________________
orange     | orange | 3
____________________________
...,...

So how should I design the fruits column in the first table so that it can accommodate getting the value from the fruit_name column in the second table String array? Since there is no array data type in MySQL, what should I do?

Best answer:

The correct way is to use multiple tables and join them in the query.

For example:

CREATE TABLE person (
`id` INT NOT NULL PRIMARY KEY,
`name` VARCHAR(50)
);
 
CREATE TABLE fruits (
`fruit_name` VARCHAR(20) NOT NULL PRIMARY KEY,
`color` VARCHAR(20),
`price` INT
);
 
CREATE TABLE person_fruit (
`person_id` INT NOT NULL,
`fruit_name` VARCHAR(20) NOT NULL,
PRIMARY KEY(`person_id`, `fruit_name`)
);

person_fruitThe table contains a row for each fruit that a person is associated with, and effectively links the people and fruits tables together.

1 | "banana"
1 | "apple"
1 | "orange"
2 | "straberry"
2 | "banana"
2 | "apple"

When you want to retrieve a person and their fruit, you can do something like this:

SELECT p.*, f.*
FROM person p
INNER JOIN person_fruit pf
ON p.id = pf.person_id
INNER JOIN fruits f
ON pf.fruit_name = f.fruit_name

Instruction one:

The reason there are no arrays in SQL is because most people don't really need it. Relational databases (that's what SQL is) work using relationships, and most of the time it's best to have one row of the table for each "bit of information". For example, you might think "I want a list of things ", instead create A new table that relates rows in one table to rows in another table. [1] In this way, you can represent M:N relationships. Another advantage is that these links do not clutter the row containing the linked item. The database can index these rows. Arrays are generally not indexed. If you don't need a relational database, you can use e.g. a key-value store. "Information. Rewritten sentence: According to the golden rule, each non-key attribute must provide corresponding information for the key and the entire key fact. "The array does too much. It has multiple facts, it stores the order (not related to the relationship itself). The performance is poor (see above).

Imagine you have a table of people and you have A table where people can make phone calls. Now you can have each person have his phone list. But each person has many other relationships with many other things. Does this mean my people table should contain every thing he is connected to? Array of things? No, that's not a property of the person itself.

[1]: If the linked table only has two columns (the primary key of each table), that's okay! If the relationship itself has other properties, it should Represent it as a column in this table.

Note two:

MySQL 5.7 now provides the JSON data type. This new data type provides a storage Convenient new methods for complex data: lists, dictionaries, etc. Object-relational mapping can be very complex because Rrays cannot be efficiently mapped to databases. In MySQL, historically people usually created tables to store lists or arrays , and add each value as a record. The table may only have 2 or 3 columns, or it may contain more. How you store this type of data really depends on the characteristics of the data.

For example, does the list contain Static or dynamic number of entries? Will the list stay small, or is it expected to grow to millions of records? Will there be a lot of reading on this table? A lot of writing? A lot of updating? These are important considerations when deciding how to store your data collection These are all factors that need to be considered.

Additionally, key:value data stores/file stores such as Cassandra, MongoDB, Redis, etc. also provide a good solution. Note where the data is actually stored (if on disk or in memory). Not all data needs to be in the same database. Some data doesn't map well to a relational database and you may have reasons to store it elsewhere, or you may want to use an in-memory key:value database as a hot cache for data stored somewhere on disk or as temporary storage Things like sessions.

The above is the detailed content of Does mysql have arrays?. For more information, please follow other related articles on the PHP Chinese website!