主页 / dba / 问题

问题[mysql-5](dba)

Martin Hope
Suzan Aydın
Asked: 2021-03-01 05:57:28 +0800 CST
  • 3

我一直在使用 SQL 开发 C# Bus System 应用程序，我需要一些建议。我不知道在这里询问是否正确，但我基本上需要有人分析我拥有的 .SQL 文件，我在其中设计了我的数据库模式。主要问题是，这是否以正确的方式设计（什么是正确的方式？），是否将 UUID 作为每个表的主键？它应该只用于机构表吗？等等

我不知道有没有什么地方我可以花他们一个小时的时间来分析它....

SQL 文件长 430 行，所以我已将其粘贴在下面，如果这不是正确的做法，我深表歉意！

谢谢^.^

-- version 5.0.2
-- https://www.phpmyadmin.net/
--
-- Host: edu-route-test-db.cqobdbj3xwim.us-east-1.rds.amazonaws.com:3306
-- Generation Time: Feb 28, 2021 at 01:45 PM
-- Server version: 8.0.20
-- PHP Version: 7.3.21

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `EduRouteDB`
--

-- --------------------------------------------------------

--
-- Table structure for table `addresses`
--

DROP TABLE IF EXISTS `addresses`;
CREATE TABLE IF NOT EXISTS `addresses` (
  `AddressId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `BuildingName` varchar(256) NOT NULL,
  `StreetName` varchar(256) NOT NULL,
  `Town` varchar(256) NOT NULL,
  `County` varchar(256) NOT NULL,
  `PostCode` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Country` varchar(256) NOT NULL,
  PRIMARY KEY (`AddressId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `bookings`
--

DROP TABLE IF EXISTS `bookings`;
CREATE TABLE IF NOT EXISTS `bookings` (
  `BookingId` binary(16) NOT NULL,
  `StudentId` binary(16) NOT NULL,
  `StartDate` date NOT NULL,
  `EndDate` date NOT NULL,
  `TimeBooked` time NOT NULL,
  `RouteId` binary(16) NOT NULL,
  `StopId` binary(16) NOT NULL,
  `UserId` binary(16) NOT NULL,
  `InstitutionId` binary(16) NOT NULL,
  PRIMARY KEY (`BookingId`),
  KEY `StudentId_Bookings_FK` (`StudentId`),
  KEY `RouteId_Bookings_FK` (`RouteId`),
  KEY `StopId_Bookings_FK` (`StopId`),
  KEY `InstitutionId_Bookings_FK` (`InstitutionId`),
  KEY `UserId_Bookings_FK` (`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `buses`
--

DROP TABLE IF EXISTS `buses`;
CREATE TABLE IF NOT EXISTS `buses` (
  `BusId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `BusName` varchar(256) NOT NULL,
  `VehicleRegistrationNumber` varchar(10) NOT NULL,
  `Capacity` int NOT NULL,
  `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (`BusId`),
  KEY `InstitutionId_Buses_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `buses_routes`
--

DROP TABLE IF EXISTS `buses_routes`;
CREATE TABLE IF NOT EXISTS `buses_routes` (
  `BusId` binary(16) NOT NULL,
  `RouteId` binary(16) NOT NULL,
  `InstitutionId` binary(16) NOT NULL,
  KEY `BusId_BusesRoutes_FK` (`BusId`),
  KEY `RouteId_BusesRoutes_FK` (`RouteId`),
  KEY `InstitutionId_BusesRoutes_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `drivers`
--

DROP TABLE IF EXISTS `drivers`;
CREATE TABLE IF NOT EXISTS `drivers` (
  `DriverId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `DriverName` varchar(256) NOT NULL,
  `BusId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `UserId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (`DriverId`),
  KEY `BusId_Drivers_FK` (`BusId`),
  KEY `InstitutionId_Drivers_FK` (`InstitutionId`),
  KEY `UserId_Drivers_FK` (`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `institutions`
--

DROP TABLE IF EXISTS `institutions`;
CREATE TABLE IF NOT EXISTS `institutions` (
  `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `InstituteName` varchar(256) DEFAULT NULL,
  `InstituteLogoFullPath` varchar(256) DEFAULT NULL,
  `InstituteJoinDate` date DEFAULT NULL,
  `IsActive` tinyint(1) DEFAULT NULL,
  `PrincipalName` varchar(256) DEFAULT NULL,
  `AddressId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (`InstitutionId`),
  KEY `AddressId_FK` (`AddressId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `parents`
--

DROP TABLE IF EXISTS `parents`;
CREATE TABLE IF NOT EXISTS `parents` (
  `ParentId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `FirstName` varchar(256) NOT NULL,
  `MiddleName` varchar(256) NOT NULL,
  `LastName` varchar(256) NOT NULL,
  `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `UserId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (`ParentId`),
  KEY `InstitutionId_Parents_FK` (`InstitutionId`),
  KEY `UserId_Parents_FK` (`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `permissions`
--

DROP TABLE IF EXISTS `permissions`;
CREATE TABLE IF NOT EXISTS `permissions` (
  `PermissionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `PermissionCategory` varchar(256) NOT NULL,
  `PermissionName` varchar(256) NOT NULL,
  PRIMARY KEY (`PermissionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `roles`
--

DROP TABLE IF EXISTS `roles`;
CREATE TABLE IF NOT EXISTS `roles` (
  `RoleId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `RoleType` varchar(256) NOT NULL,
  `InstitutionId` binary(16) NOT NULL,
  PRIMARY KEY (`RoleId`),
  KEY `InstitutionId_Roles_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `roles_permissions`
--

DROP TABLE IF EXISTS `roles_permissions`;
CREATE TABLE IF NOT EXISTS `roles_permissions` (
  `RoleId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `PermissionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  KEY `RoleId_Roles_Permissions_FK` (`RoleId`),
  KEY `PermissionId_Roles_Permissions_FK` (`PermissionId`),
  KEY `InstitutionId_Roles_Permissions_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `routes`
--

DROP TABLE IF EXISTS `routes`;
CREATE TABLE IF NOT EXISTS `routes` (
  `RouteId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `RouteName` varchar(256) NOT NULL,
  `Mon` tinyint(1) NOT NULL DEFAULT '0',
  `Tue` tinyint(1) NOT NULL DEFAULT '0',
  `Wed` tinyint(1) NOT NULL DEFAULT '0',
  `Thu` tinyint(1) NOT NULL DEFAULT '0',
  `Fri` tinyint(1) NOT NULL DEFAULT '0',
  `Sat` tinyint(1) NOT NULL DEFAULT '0',
  `Sun` tinyint(1) NOT NULL DEFAULT '0',
  `StartDate` date NOT NULL,
  `EndDate` date NOT NULL,
  `StartTime` time NOT NULL,
  `EndTime` time NOT NULL,
  `CurrentNumberOfBookings` int NOT NULL,
  `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (`RouteId`),
  KEY `InstitutionId_Routes_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `routes_stops`
--

DROP TABLE IF EXISTS `routes_stops`;
CREATE TABLE IF NOT EXISTS `routes_stops` (
  `RouteId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `StopId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `Time` time NOT NULL,
  `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  KEY `RouteId_RoutesStops_FK` (`RouteId`),
  KEY `StopId_RoutesStops_FK` (`StopId`),
  KEY `InstitutionId_RoutesStops_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `staff`
--

DROP TABLE IF EXISTS `staff`;
CREATE TABLE IF NOT EXISTS `staff` (
  `StaffId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `FirstName` varchar(256) NOT NULL,
  `MiddleName` varchar(256) NOT NULL,
  `LastName` varchar(256) NOT NULL,
  `UserId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  KEY `UserId_FK` (`UserId`),
  KEY `InstitutionId_Staff_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `stops`
--

DROP TABLE IF EXISTS `stops`;
CREATE TABLE IF NOT EXISTS `stops` (
  `StopId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `StopName` varchar(256) NOT NULL,
  `Longitude` decimal(9,6) NOT NULL,
  `Latitude` decimal(9,6) NOT NULL,
  `AddressId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (`StopId`),
  KEY `AddressId_Stops_FK` (`AddressId`),
  KEY `InstitutionId_Stops_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `students`
--

DROP TABLE IF EXISTS `students`;
CREATE TABLE IF NOT EXISTS `students` (
  `StudentId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `FirstName` varchar(256) NOT NULL,
  `MiddleName` varchar(256) NOT NULL,
  `LastName` varchar(256) NOT NULL,
  `DateOfBirth` date NOT NULL,
  `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (`StudentId`),
  KEY `InstitutionId_Student_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `students_parents`
--

DROP TABLE IF EXISTS `students_parents`;
CREATE TABLE IF NOT EXISTS `students_parents` (
  `StudentId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `ParentId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  KEY `StudentId_StudentParent_FK` (`StudentId`),
  KEY `ParentId_StudentParent_FK` (`ParentId`),
  KEY `InstitutionId_StudentParent_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `UserId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `Email` varchar(256) DEFAULT NULL,
  `Password` char(60) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  `RoleId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (`UserId`),
  KEY `InstitutionId_FK` (`InstitutionId`),
  KEY `RoleId_Users_FK` (`RoleId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `bookings`
--
ALTER TABLE `bookings`
  ADD CONSTRAINT `InstitutionId_Bookings_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `RouteId_Bookings_FK` FOREIGN KEY (`RouteId`) REFERENCES `routes_stops` (`RouteId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `StopId_Bookings_FK` FOREIGN KEY (`StopId`) REFERENCES `routes_stops` (`StopId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `StudentId_Bookings_FK` FOREIGN KEY (`StudentId`) REFERENCES `students` (`StudentId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `UserId_Bookings_FK` FOREIGN KEY (`UserId`) REFERENCES `users` (`UserId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `buses`
--
ALTER TABLE `buses`
  ADD CONSTRAINT `InstitutionId_Buses_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `buses_routes`
--
ALTER TABLE `buses_routes`
  ADD CONSTRAINT `BusId_BusesRoutes_FK` FOREIGN KEY (`BusId`) REFERENCES `buses` (`BusId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `InstitutionId_BusesRoutes_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `RouteId_BusesRoutes_FK` FOREIGN KEY (`RouteId`) REFERENCES `routes` (`RouteId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `drivers`
--
ALTER TABLE `drivers`
  ADD CONSTRAINT `BusId_Drivers_FK` FOREIGN KEY (`BusId`) REFERENCES `buses` (`BusId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `InstitutionId_Drivers_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `UserId_Drivers_FK` FOREIGN KEY (`UserId`) REFERENCES `users` (`UserId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `institutions`
--
ALTER TABLE `institutions`
  ADD CONSTRAINT `AddressId_FK` FOREIGN KEY (`AddressId`) REFERENCES `addresses` (`AddressId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `parents`
--
ALTER TABLE `parents`
  ADD CONSTRAINT `InstitutionId_Parents_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `UserId_Parents_FK` FOREIGN KEY (`UserId`) REFERENCES `users` (`UserId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `roles`
--
ALTER TABLE `roles`
  ADD CONSTRAINT `InstitutionId_Roles_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `roles_permissions`
--
ALTER TABLE `roles_permissions`
  ADD CONSTRAINT `InstitutionId_Roles_Permissions_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `PermissionId_Roles_Permissions_FK` FOREIGN KEY (`PermissionId`) REFERENCES `permissions` (`PermissionId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `RoleId_Roles_Permissions_FK` FOREIGN KEY (`RoleId`) REFERENCES `roles` (`RoleId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `routes`
--
ALTER TABLE `routes`
  ADD CONSTRAINT `InstitutionId_Routes_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `routes_stops`
--
ALTER TABLE `routes_stops`
  ADD CONSTRAINT `InstitutionId_RoutesStops_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `RouteId_RoutesStops_FK` FOREIGN KEY (`RouteId`) REFERENCES `routes` (`RouteId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `StopId_RoutesStops_FK` FOREIGN KEY (`StopId`) REFERENCES `stops` (`StopId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `staff`
--
ALTER TABLE `staff`
  ADD CONSTRAINT `InstitutionId_Staff_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `UserId_FK` FOREIGN KEY (`UserId`) REFERENCES `users` (`UserId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `stops`
--
ALTER TABLE `stops`
  ADD CONSTRAINT `AddressId_Stops_FK` FOREIGN KEY (`AddressId`) REFERENCES `addresses` (`AddressId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `InstitutionId_Stops_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `students`
--
ALTER TABLE `students`
  ADD CONSTRAINT `InstitutionId_Student_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `students_parents`
--
ALTER TABLE `students_parents`
  ADD CONSTRAINT `InstitutionId_StudentParent_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ParentId_StudentParent_FK` FOREIGN KEY (`ParentId`) REFERENCES `parents` (`ParentId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `StudentId_StudentParent_FK` FOREIGN KEY (`StudentId`) REFERENCES `students` (`StudentId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `InstitutionId_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `RoleId_Users_FK` FOREIGN KEY (`RoleId`) REFERENCES `roles` (`RoleId`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
mysql-5
Martin Hope
Jeff Michael
Asked: 2015-02-07 10:16:40 +0800 CST
  • 0

我几乎是一个初学者，只创建了一些依赖 MySQL 数据库的小型 php web 应用程序。我现在正在从事一个更大的项目。我正处于建立数据库的计划阶段，很难全神贯注于一个问题。

该数据库将用于乐高。主表将是零件和套装。在套装表下，我需要列出每个套装中的零件号以及套装中这些零件的数量。我对如何设置的知识有限，我会设置类似...part_1、part_2、part_3...等& qty_1、qty_2、qty_3...等。问题是会有一些集合有数百个部分，这使得我能想到的方法变得非常混乱。此外，我希望为用户提供能够查询数据库并恢复具有特定部分的所有集合的功能。使用我想到的设置，我无法尽可能地看到它，因为它可能需要搜索 100 多个列。

我确信有一个简单的解决方案是我的菜鸟看不到的，而且我无法通过我一直在进行的关键字搜索在线找到一个解决方案（我进行了 4 小时的搜索并且学到了很多东西但不是我想要的......） . 任何建议将不胜感激。

抱歉没有给你代码。该网站将使用 PHP 和 MySQL 数据库来完成。目前我正处于在笔记本上画图的阶段，没有任何编码。

mysql-5
Martin Hope
Rob D
Asked: 2015-01-15 10:02:18 +0800 CST
  • 1

我有一个包含超过 600 万条记录的日志表。我想要一个查询，向我显示src去往同一目的地/端口的所有事件。

我试过这个：

SELECT src, dst, dstport, COUNT(src) AS Hits 
FROM logs  
GROUP BY src 
ORDER BY Hits DESC;

不确定这个查询是否给了我我想要的。此外，排除特定端口的最佳方法是什么？说dstport = 53？

我希望每次每个 src 到达相同的 dst 和 dstport 时都显示，同时保持对事件的计数。说 src 10.110.0.10 到达 dst 10.2.9.124:53 2,345,568 次 & 也到达 192.168.9.18:80 174 次；那么结果将如下所示：示例：

+-----------------+-----------------+---------+---------+ | src | dst | dstport | Hist | +-----------------+-----------------+---------+---------+ | 10.110.0.10 | 10.2.9.124 | 53 | 2345568 | +-----------------+-----------------+---------+---------+ | 10.110.0.10 | 192.168.9.18 | 80 | 174 | +-----------------+-----------------+---------+---------+

但是通过上面的查询我得到了这个结果：

+-----------------+-----------------+---------+---------+ | src | dst | dstport | Hist | +-----------------+-----------------+---------+---------+ | 10.110.0.10 | 10.2.9.124 | 53 | 1443780 | | 10.110.0.10 | 192.168.9.124 | 53 | 1402210 | | 10.192.31.23 | 10.192.1.120 | 8082 | 319507 | | 10.192.31.19 | 10.192.1.186 | 8081 | 319203 | | 192.168.31.131 | 192.168.31.130 | 80 | 290818 | +-----------------+-----------------+---------+---------+

mysql mysql-5
Martin Hope
Jakub Turcovsky
Asked: 2014-09-11 04:01:48 +0800 CST
  • 0

我需要以某种方式检查查询结果是否是NULLMySQL 返回错误Subquery returns more than 1 row。请问我应该如何重写它才能正常工作？

SELECT DISTINCT user.id, user.username, user.name, user.surname, user.avatar,
(SELECT IF(ISNULL(
  (SELECT attendant.fk_user AS u
  FROM attendant
  WHERE fk_event = 1

  UNION

  SELECT organizer.fk_user AS u
  FROM organizer
  WHERE fk_event = 1

  UNION

  SELECT invitation.fk_user_to AS u
  FROM invitation
  WHERE fk_event = 1)), NULL, 1)) AS related
  ...
mysql mysql-5
Martin Hope
Kaoru
Asked: 2014-06-13 01:56:29 +0800 CST
  • 1

我刚刚pager在 MySQL 命令行客户端中发现了这个命令，我玩得很开心。这篇博文很棒：

http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

不幸的是，作为一名 Perl 程序员，我发现自己想做越来越复杂的寻呼机，但我遇到了一个问题：我似乎无法在寻呼机命令中使用分号字符。

简单的：

mysql> pager md5sum
PAGER set to 'md5sum'
mysql> select id from listings limit 5;
cfbe10c0a46ea57f6fa199bc9e05ed3a  -

更复杂但仍然很好：

mysql> pager perl -nle 'print "    $_"'
PAGER set to 'perl -nle 'print "    $_"''
mysql> select id from listings limit 5;
    +---------+
    | id      |
    +---------+
    | 2003921 |
    | 7164746 |
    | 7166576 |
    | 7166605 |
    | 7166853 |
    +---------+

（那个对于处理 Markdown 很有用！）

但不幸的是，如果我在语句的末尾添加一个分号，它会说我有一个语法错误：

mysql> pager perl -nle 'print "    $_";'
    -> \c

mysql> pager perl -nle 'print "    $_";';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pager perl -nle 'print "    $_";'' at line 1

我尝试用一​​两个反斜杠转义 Perl 命令中的分号（以防反斜杠本身需要转义），但似乎没有任何效果。

是否可以在寻呼机命令中包含分号？

mysql mysql-5
Martin Hope
krammer
Asked: 2013-01-18 22:39:58 +0800 CST
  • 0

我有一个使用 MySQL 5.0 的主从设置。我非常频繁地写入主机，而从机仅用于备份目的（无显式写入）。

在我当前的配置中，从数据库通过 VPN 自动由 MySQL 与主数据库同步。但是如果由 Master 组成的系统（win 7）由于某种原因宕机，重启后同步不会从上次同步的记录继续。相反，我必须停止两端的服务，删除并重新创建配置文件，将备份的 sql 从主服务器移动到从服务器并重新启动同步。每当我的系统因任何原因宕机时，都必须执行所有这些操作。

是否有可能避免这种情况，以便每当主系统出现故障时，同步会像往常一样再次开始？

mysql mysql-5
Martin Hope
seed_87
Asked: 2012-08-18 05:40:08 +0800 CST
  • 1

我的老板想在运行 mysql 的服务器上记录用户活动。

有一些像：

  1. 用户blah 已登录
  2. 用户blah运行了一些查询
  3. 用户blah已注销
  4. 用户asdf 已登录
  5. ..等等

我做了一些研究，发现了触发器： 如何记录成功的登录尝试到数据库？

此外，该信息是否已记录到 mysql 常规日志中？

更多信息：

  • mysql 安装在运行 Redhat 的服务器上
  • 安装的mysql版本为：5.0.91

我不是DBA，所以请放轻松！提前谢谢！

mysql mysql-5