I have a variable of type array and I want to store it as it is in database(mysql) and when I apply tinyblob that I usually use in java for display it return abnormal output. So what is the correct datatype for the array in php.
我有一个类型数组的变量,我想存储它,因为它在数据库(mysql)中,当我应用tinyblob,我通常在java中用于显示它返回异常输出。那么php中数组的正确数据类型是什么?
$countryGoup=array("USA","FRANCE","GERMANY","BRITAIN");
1 个解决方案
#1
12
You could:
1) use a SET datatype if the values are few and fixed
You define the column in the CREATE TABLE statement as SET('USA', 'France', 'Germany', ...) NOT NULL (not null optional), and use queries like:
您将CREATE TABLE语句中的列定义为SET('USA','France','Germany',...)NOT NULL(非null可选),并使用如下查询:
INSERT INTO myTable SET countries="USA,Germany";
2) use a lookup table with a foreign reference
So you define a countries table:
所以你定义一个国家表:
id_country|name
----------+-----------
1 |USA
2 |Germany
3 |France
and define a pivot table that links your main object (f.ex. "users") to the countries, thus esabilishing a many-to-many relationship.
并定义一个数据透视表,将您的主要对象(例如“用户”)链接到国家/地区,从而实现多对多关系。
F.ex., a users_countries table would link users table to countries table in this way:
F.ex.,users_countries表将以这种方式将users表链接到countries表:
id_user|id_country
------------------
1 |1
1 |2
2 |1
In this example, user 1 is linked to countries with id 1 and 2 (USA and Germany), user 2 is linked to USA only, and so on
在此示例中,用户1链接到ID为1和2(美国和德国)的国家/地区,用户2仅链接到美国,依此类推
3) use a comma-separated string, or other separators, or serialize the array
This involves using the server-side language (in your case PHP) to concat the values with some separator (such as comma , or pipe |):
这涉及使用服务器端语言(在您的情况下为PHP)将值与某些分隔符(例如逗号或管道|)连接起来:
$countries = array("USA", "Russia", "Iran");
$countriesString = implode (",", $countries); // $countriesString = "Usa,Russia,Iran"
$query = "INSERT INTO mytable SET countries = '" . $countriesString . "'";
(Note: the string value is not escaped for example purpose, but it is not a good practice)
(注意:字符串值不会出于示例目的而被转义,但这不是一个好习惯)
When you retrieve the value from the db, you retrive the string you put into, and obtain again an array with $array = explode(",", $retrievedValueFromDb), providing the same separator.
从db中检索值时,将检索放入的字符串,并再次获取带有$ array = explode(“,”,$ retrievalValueFromDb)的数组,从而提供相同的分隔符。
Update:
4) Retrieve values find_in_set() MySQL function
You can rely also on the find_in_set(needle, field) function, that returns an integer greater than zero if the string is present in a comma-separated value field.
您还可以依赖find_in_set(needle,field)函数,如果字符串存在于逗号分隔值字段中,则该函数返回大于零的整数。
So if you use approach 1) and have a string field like "USA,Germany", you can obtain the columns in which USA is present by using:
因此,如果您使用方法1)并且具有类似“USA,Germany”的字符串字段,则可以使用以下方法获取USA所在的列:
SELECT * FROM myTable WHERE find_in_set('USA', countries) <> 0
Approach 1) works if you have few fixed values, also very easy on queries. Downside is that you are limited to 64 values, that the values must be fixed, and the cannot contain a comma (,) as its used as a separator.
方法1)如果你有很少的固定值,也很容易查询。缺点是您必须限制为64个值,必须修复这些值,并且不能包含逗号(,)作为分隔符。
Approach 2) is the most standardized and correct, it's also normalized and query-efficient
方法2)是最标准化和正确的,它也是规范化和查询效率的
Approach 3) is easy but requires extra processing and is not so easy for SQL
方法3)很简单,但需要额外的处理,对SQL来说并不那么容易
As stated elsewhere, there is no "array" datatype in the dbms, you have to use some workaround.
如其他地方所述,dbms中没有“数组”数据类型,您必须使用一些解决方法。
#1
12
You could:
1) use a SET datatype if the values are few and fixed
You define the column in the CREATE TABLE statement as SET('USA', 'France', 'Germany', ...) NOT NULL (not null optional), and use queries like:
您将CREATE TABLE语句中的列定义为SET('USA','France','Germany',...)NOT NULL(非null可选),并使用如下查询:
INSERT INTO myTable SET countries="USA,Germany";
2) use a lookup table with a foreign reference
So you define a countries table:
所以你定义一个国家表:
id_country|name
----------+-----------
1 |USA
2 |Germany
3 |France
and define a pivot table that links your main object (f.ex. "users") to the countries, thus esabilishing a many-to-many relationship.
并定义一个数据透视表,将您的主要对象(例如“用户”)链接到国家/地区,从而实现多对多关系。
F.ex., a users_countries table would link users table to countries table in this way:
F.ex.,users_countries表将以这种方式将users表链接到countries表:
id_user|id_country
------------------
1 |1
1 |2
2 |1
In this example, user 1 is linked to countries with id 1 and 2 (USA and Germany), user 2 is linked to USA only, and so on
在此示例中,用户1链接到ID为1和2(美国和德国)的国家/地区,用户2仅链接到美国,依此类推
3) use a comma-separated string, or other separators, or serialize the array
This involves using the server-side language (in your case PHP) to concat the values with some separator (such as comma , or pipe |):
这涉及使用服务器端语言(在您的情况下为PHP)将值与某些分隔符(例如逗号或管道|)连接起来:
$countries = array("USA", "Russia", "Iran");
$countriesString = implode (",", $countries); // $countriesString = "Usa,Russia,Iran"
$query = "INSERT INTO mytable SET countries = '" . $countriesString . "'";
(Note: the string value is not escaped for example purpose, but it is not a good practice)
(注意:字符串值不会出于示例目的而被转义,但这不是一个好习惯)
When you retrieve the value from the db, you retrive the string you put into, and obtain again an array with $array = explode(",", $retrievedValueFromDb), providing the same separator.
从db中检索值时,将检索放入的字符串,并再次获取带有$ array = explode(“,”,$ retrievalValueFromDb)的数组,从而提供相同的分隔符。
Update:
4) Retrieve values find_in_set() MySQL function
You can rely also on the find_in_set(needle, field) function, that returns an integer greater than zero if the string is present in a comma-separated value field.
您还可以依赖find_in_set(needle,field)函数,如果字符串存在于逗号分隔值字段中,则该函数返回大于零的整数。
So if you use approach 1) and have a string field like "USA,Germany", you can obtain the columns in which USA is present by using:
因此,如果您使用方法1)并且具有类似“USA,Germany”的字符串字段,则可以使用以下方法获取USA所在的列:
SELECT * FROM myTable WHERE find_in_set('USA', countries) <> 0
Approach 1) works if you have few fixed values, also very easy on queries. Downside is that you are limited to 64 values, that the values must be fixed, and the cannot contain a comma (,) as its used as a separator.
方法1)如果你有很少的固定值,也很容易查询。缺点是您必须限制为64个值,必须修复这些值,并且不能包含逗号(,)作为分隔符。
Approach 2) is the most standardized and correct, it's also normalized and query-efficient
方法2)是最标准化和正确的,它也是规范化和查询效率的
Approach 3) is easy but requires extra processing and is not so easy for SQL
方法3)很简单,但需要额外的处理,对SQL来说并不那么容易
As stated elsewhere, there is no "array" datatype in the dbms, you have to use some workaround.
如其他地方所述,dbms中没有“数组”数据类型,您必须使用一些解决方法。