Does mysql have arrays?-Mysql Tutorial-php.cn

Table of Contents

1. Store arrays in the form of strings in MySQL

2.MySQL中存储数组（list）的示例

说明一：

说明二：

Home

Database

Mysql Tutorial

Does mysql have arrays?

青灯夜游

Mar 30, 2023 pm 08:15 PM

mysql

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. Relational databases 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, 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.

Does mysql have arrays?

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

1. Store arrays in the form of strings in MySQL

There are no array types in MySQL. Array elements are usually divided by a certain character and stored in the form of strings

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 method is very simple, just look at how many delimiters exist in the string, and then add one to get the required result.

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

Copy after login

Calling example:

select  Get_StrArrayLength(&#39;78,1,2,3&#39;,&#39;,&#39;)

Copy after login

Return value:

Copy after login

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

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

Copy after login

Calling example:

select  Get_StrArrayStrOfIndex(&#39;8,9,4&#39;,&#39;,&#39;,2)

Copy after login

Return value:

Copy after login

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

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

Copy after login

调用结果：

Copy after login

2.MySQL中存储数组（list）的示例

我在MySQL中有两个表。表Person具有以下列：

id | name | fruits

Copy after login

水果列可以包含空或像(‘apple’，’orange’，’banana’)或(‘strawberry’)等的字符串数组。第二个表是Table Fruit，有以下三列：

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

Copy after login

那么我应该如何设计第一个表中的fruits列，以便它可以容纳从第二个表中的fruit_name列获取值的字符串数组？由于MySQL中没有数组数据类型，我该怎么办呢？

最佳答案：

正确的方法是使用多个表，并在查询中加入它们。

例如：

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`)
);

Copy after login

person_fruit表包含一个人与其相关联的每个水果的一行，并且有效地将人和水果表链接在一起。

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

Copy after login

当你想检索一个人和他们的水果，你可以做这样的事情：

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

Copy after login

说明一：

SQL中没有数组的原因是因为大多数人并不真正需要它。关系数据库（SQL就是这样）使用关系工作，并且大多数情况下，最好是为每个“信息位”分配一行表。例如，你可能认为“我想要一个东西列表”，而是创建一个新表，将一个表中的行与另一个表中的行相关联。[1] 这样，您可以表示M：N关系。另一个优点是这些链接不会使包含链接项的行混乱。数据库可以索引这些行。数组通常不会编入索引。

如果您不需要关系数据库，则可以使用例如键值存储。

黄金法则是“[每个]非关键[属性]必须提供关于密钥，整个密钥以及密钥的事实。” 数组做得太多了。它有多个事实，它存储订单（与关系本身无关）。性能很差（见上文）。

想象一下，你有一张人桌，你有一张桌子，可以让人打电话。现在你可以让每个人都有他的电话列表。但每个人与许多其他事物有许多其他关系。这是否意味着我的人员表应该包含他连接的每一件事物的数组？不，这不是这个人本身的属性。

[1]：如果链接表只有两列（每个表的主键），这没关系！如果关系本身具有其他属性，则应在此表中将其表示为列。

说明二：

MySQL 5.7现在提供JSON数据类型。这种新的数据类型提供了一种存储复杂数据的便捷新方法：列表，字典等。

也就是说，rrays不能很好地映射数据库，这就是对象关系映射可能非常复杂的原因。历史上，人们通过创建描述它们的表并将每个值添加为自己的记录来在MySQL中存储列表/数组。该表可能只有2或3列，或者可能包含更多列。如何存储此类数据实际上取决于数据的特征。

例如，列表是否包含静态或动态条目数？该列表是否会保持较小，或者预计会增长到数百万条记录？这张桌子上会有很多读物吗？很多写作？很多更新？在决定如何存储数据集合时，这些都是需要考虑的因素。

此外，密钥：价值数据存储/文件存储，如Cassandra，MongoDB，Redis等也提供了一个很好的解决方案。请注意数据实际存储的位置（如果存储在磁盘或内存中）。并非所有数据都需要位于同一数据库中。某些数据无法很好地映射到关系数据库，您可能有理由将其存储在其他位置，或者您可能希望使用内存中的键：值数据库作为存储在磁盘某处或作为临时存储的数据的热缓存像会话这样的东西。

【相关推荐：mysql视频教程】

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

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Roblox: Grow A Garden - Complete Mutation Guide

3 weeks ago By DDD

How to fix KB5055612 fails to install in Windows 10?

3 weeks ago By DDD

Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Nordhold: Fusion System, Explained

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Mandragora: Whispers Of The Witch Tree - How To Unlock The Grappling Hook

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial

1666

CakePHP Tutorial

1426

Laravel Tutorial

1328

PHP Tutorial

1273

C# Tutorial

1255

Related knowledge

Laravel Introduction Example Apr 18, 2025 pm 12:45 PM

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

MySQL and phpMyAdmin: Core Features and Functions Apr 22, 2025 am 12:12 AM

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.

MySQL vs. Other Programming Languages: A Comparison Apr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Solve database connection problem: a practical case of using minii/db library Apr 18, 2025 am 07:09 AM

I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.

Laravel framework installation method Apr 18, 2025 pm 12:54 PM

Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.

Solve MySQL mode problem: The experience of using the TheliaMySQLModesChecker module Apr 18, 2025 am 08:42 AM

When developing an e-commerce website using Thelia, I encountered a tricky problem: MySQL mode is not set properly, causing some features to not function properly. After some exploration, I found a module called TheliaMySQLModesChecker, which is able to automatically fix the MySQL pattern required by Thelia, completely solving my troubles.

Explain the purpose of foreign keys in MySQL. Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

Compare and contrast MySQL and MariaDB. Apr 26, 2025 am 12:08 AM

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

See all articles