MySQL——我应该使用什么数据类型来存储一组字符串。

I want to create a table that stores doctors. My problem is how i will store the specialities of a doctor as a doctor can have more than one (aphrodisiologist,dermatologist). So far i have thougth of using varchar type and storind the values in comma separated format.

我想创建一个存放医生的表格。我的问题是，我如何将医生的专业知识储存起来，因为医生可以拥有不止一个(春药专家、皮肤科医生)。到目前为止，我使用了varchar类型，并以逗号分隔的格式存储了值。

CREATE TABLE IF NOT EXISTS `doctors` (
  `doctor_id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `password` varchar(30) NOT NULL,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `speciality` varchar(254) NOT NULL,
  `lat` decimal(10,8) NOT NULL,
  `lng` decimal(10,8) NOT NULL,
  `phone` char(10) NOT NULL,
  `mobile` char(10) NOT NULL,
  PRIMARY KEY (`doctor_id`)
);

But my problem is when quering the table. If i want to find the doctors that have the speciality 'alfa' and i do this

但我的问题是，当你问我问题的时候。如果我想找到有特殊“阿尔法”的医生，我就这么做

select * from doctors where speciality='alfa'

the doctor that has two specialities (alfa,beta) will not be included in the result. The only thing i have thought is to use LIKE instead of WHERE but i think that there must be a better way. What is the best way to implement this (perhaps a different data type from varchar)?

有两个专业(alfa,beta)的医生不会被包括在结果中。我唯一的想法是用LIKE而不是WHERE，但我认为一定有更好的方法。实现这个的最佳方式是什么(可能是与varchar不同的数据类型)?

3 个解决方案

#1

This is a many-to-many relationship. Each doctor can potentially have multiple specialties, and many doctors have each specialty.

这是一个多对多关系。每个医生都可能有多个专长，许多医生都有各自的专长。

Doctors >--------< Specialties

The way to represent a many-to-many relationship in a relational database is not with a comma-separated list, but with another table.

在关系数据库中表示多对多关系的方法不是使用逗号分隔的列表，而是使用另一个表。

Doctors ----< HasSpecialty >---- Specialties

Here's an example:

这里有一个例子:

CREATE TABLE HasSpecialty (
  specialty_id INT,
  doctor_id INT,
  PRIMARY KEY (specialty_id, doctor_id),
  KEY (doctor_id, specialty_id),
  FOREIGN KEY (doctor_id) REFERENCES Doctors(doctor_id),
  FOREIGN KEY (specialty_id) REFERENCES Specialties(specialty_id)
);

Querying for doctors with a given specialty, do a JOIN:

查询具有特定专业的医生，加入:

SELECT d.* FROM Doctors AS d
INNER JOIN HasSpecialty AS hs USING (doctor_id)
INNER JOIN Specialties AS s USING (specialty_id)
WHERE s.specialty = 'alfa';

Some people dislike JOINs because they think they are slow. They go to great lengths to avoid JOIN, such as using dependent subqueries which can be worse. With proper indexes, joins are not slow.

有些人不喜欢加入，因为他们认为他们很慢。它们不遗余力地避免连接，例如使用可能更糟糕的从属子查询。使用适当的索引，连接并不慢。

#2

You can use type SET http://dev.mysql.com/doc/refman/5.5/en/set.html Then you can find data even use bit field mappings. But be careful this type has some limitations. Take a look at documentation.

您可以使用类型设置http://devmy.sql.com/doc/refman/5.5 /en/set.html，然后您甚至可以使用位字段映射找到数据。但是要注意这种类型有一些局限性。看一下文档。

#3

Create a table of doctor_specialties with these columns:

用以下列创建一个doctor_specialty表:

doctor_id int not null
specialty varchar(254) not null

The doctor_id should be a foreign key to doctors, and consider making a 2-column primary key on both columns.

doctor_id应该是医生的外键，并考虑在两列上创建一个2列的主键。

Insert records in this table, referencing the doctor and their specialty. You would have multiple records if the doctor has multiple specialties.

在该表中插入记录，引用医生和他们的专业。如果医生有多个专业，你会有多个记录。

Then, to query doctors that have a certain specialty, you could do:

然后，要询问有特定专业的医生，你可以这样做:

select * from doctors d 
where exists (select * from doctor_specialties 
              where doctor_id = d.doctor_id 
                  and specialty = 'alfa');

There are a number of ways to write this query, including sub-selects or joins. I use EXISTS when I don't need to return the information in the other table.

有许多方法可以编写这个查询，包括子选择或连接。当不需要返回另一个表中的信息时，我使用exist。

#1