MySQL查询帮助. [英] MySQL query Help.

问题描述

好吧，我有一个这样的表:

Well I have a table like this:

CREATE TABLE IF NOT EXISTS `rb_media` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Category` int(11) NOT NULL,
`FilePath` varchar(255) NOT NULL,
`SongArtist` varchar(100) NOT NULL,
`SongName` varchar(100) NOT NULL,
`SongLengh` bigint(20) NOT NULL,
`LastPlay` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

和

and

CREATE TABLE IF NOT EXISTS `rb_categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(255) NOT NULL,
`PreCategoryID` varchar(255) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2

我不想执行以下查询:
选择*来自"rb_media"，ID = 432；

然后:
从"rb_categories"中选择名称，其中id =类别

仅执行一个查询的解决方案吗?

[添加了适当的代码格式]

I don''t want to do a query like:
SELECT * FROM ''rb_media'' WHERE id=432;

And then:
SELECT name FROM ''rb_categories'' WHERE id=Category

Any Solution to only do one query?

[Added appropriate code formatting]

推荐答案

您选择使用myisam意味着您将无法强制执行良好的数据(您的媒体表可以包含在您的类别表中不存在.)

但是您要查找的查询是

your choice to use myisam means that you won''t be able to enforce good data (your media table can contain category ids that don''t exist in your category table).

but the query you are looking for is

select c.`name` from `rb_media` m inner join `rb_categories` c
on m.Category = c.id
where m.id = 432

谢谢，是其他选择吗?你说我正在使用MyISAM?我总是使用那个引擎.我必须阅读其他有关immoDB的文章. :-O

Thanks, Wich is the other option? That you say that I am using MyISAM? I always use that engine. I have to read about others like immoDB. :-O

要确保其他表中存在类别"，您需要使用外键.这意味着InnoDB是您唯一的选择.

MySQL中的外键实际上只是对数据的强制执行在InnoDB上.其他引擎提供外键仅出于兼容性目的，对数据没有影响"(我现在找不到引号.)

尽管在此页面上，我发现这可以支持我的断言.

To make sure the "Category" to exists in your other table you need to a foreign key. Which means InnoDB is your only option.

Foreign keys in MySQL only actually do the expected enforcement of data on InnoDB. Other engines provide foreign keys "for compatibility only and have no effect on data" (I can''t find that quote right now. )

Though on this page I found this to support my assertion.

对于InnoDB以外的存储引擎，MySQL Server在CREATE TABLE语句中解析FOREIGN KEY语法，但不使用或存储它.将来，将扩展实现以将该信息存储在表规范文件中，以便mysqldump和ODBC可以检索该信息.在稍后的阶段，还将为MyISAM表实现外键约束.

For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it. In the future, the implementation will be extended to store this information in the table specification file so that it may be retrieved by mysqldump and ODBC. At a later stage, foreign key constraints will be implemented for MyISAM tables as well.

这篇关于MySQL查询帮助.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！