数据库学习(MySQL)经典CAP数据库关于主键和外键在表建好以后的添加中遇到的问题及处理

本文记录了在MySQL中为orders表添加外键时遇到的兼容性问题及解决过程。问题在于agents和products表的字符集与customers表不一致,以及products表缺少主键pid。通过调整字符集和补充主键,成功解决了外键添加失败的问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、遇到的问题:在orders,agents,customers,products都建立好后在orders表中添加外键一个成功,三个失败

mysql> ALTER TABLE ORDERS
    -> ADD FOREIGN KEY(cid) REFERENCES CUSTOMERS(cid);
Query OK, 16 rows affected (0.12 sec)
Records: 16  Duplicates: 0  Warnings: 0

添加cid外键成功

mysql> ALTER TABLE ORDERS
    -> ADD FOREIGN KEY(aid) REFERENCES AGENTS(aid);
ERROR 3780 (HY000): Referencing column 'aid' and referenced column 'aid' in foreign key constraint 'orders_ibfk_2' are incompatible.

添加aid外键失败

mysql> ALTER TABLE ORDERS
    -> ADD FOREIGN KEY(pid)
    -> REFERENCES PRODUCTS(pid);
ERROR 3780 (HY000): Referencing column 'pid' and referenced column 'pid' in foreign key constraint 'orders_ibfk_2' are incompatible.

添加pid外键也失败

 

二、开始找问题

mysql> SHOW CREATE TABLE table_name;

用这个命令来看哪里incompatible了。。。

然后我就发现

orders是这样的

以下分别是agents customers products

一对比,就发现了几个错误,引擎都是默认的innodb,成功的customers表的字符集是utf8,没有后面一长串的,当时建表的时候只有两张表也就是orders和customers表是按照廖雪峰老师的代码来的,也就是建表的时候就设置了引擎和字符集,剩下的两张表products和agents都没有在新建表的时候添加,是软件默认添加的,所以有一长串,然后就incompatible

修改代码:

mysql> alter table agents convert to character set utf8;
Query OK, 6 rows affected, 1 warning (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 1
mysql> ALTER TABLE PRODUCTS CONVERT TO CHARACTER SET UTF8;
Query OK, 7 rows affected, 1 warning (0.10 sec)
Records: 7  Duplicates: 0  Warnings: 1

并且products表还有个问题,建表的时候的疏漏

missing index 

我发现就是忘了给products表建主键pid了

后来加上pid主键 再给orders表添加aid和pid外键就都成功了

 

今天的学习就到这儿吧 还是动手印象深

昨晚因为MySQL出问题捣鼓一整晚,今早放弃,卸干净,重新装的

cmd要管理者权限才能开服务  

路漫漫其修远兮 我的数据库学习之路

<think>嗯,用户让我设计一个MySQL数据库来存储投放广告的信息。首先,我需要确定广告的基本属性。广告通常有名称、内容、类型,可能还有图片或视频的URL,对吧?然后,投放的时间段很重要,所以需要开始时间结束时间。状态字段来标记是否启用或者暂停,这样管理员可以控制广告的显示。 接下来,目标受众可能涉及地理位置、语言、设备类型这些维度。比如广告可能只针对某个地区的用户,或者特定的设备类型,比如手机或桌面。这些可能需要用JSON字段来存储,因为结构可能比较复杂,而且不同广告的条件不同。 计费方式方面,可能有按点击付费(CPC)、按展示付费(CPM)或者按行动付费(CPA)。这里可以用枚举类型来限制可选值,确保数据的有效性。 还需要关联广告主的信息,所以广告主ID是必须的,指向广告主。预算已消耗金额用来跟踪广告的花费情况,防止超支。投放次数点击次数用于统计效果,这些字段需要经常更新。 主键的话,使用自增的ID比较合适。索引方面,广告主ID、开始时间结束时间,以及状态字段可能会经常被查询,加上索引可以提高查询效率。时间戳记录创建更新时间,方便追踪数据变更。 然后,考虑到扩展性,比如JSON字段可以灵活添加新的目标条件而不需要修改结构。约束确保广告主存在,保持数据一致性。预算消耗的检查约束,虽然MySQL不支持,但可以在应用层处理,确保消耗不超过预算。 最后,可能需要补充其他信息,比如优先级或者审核状态,根据实际需求调整。总结下来,结构需要涵盖广告的基本信息、投放设置、目标受众、统计数据系统记录,确保功能全面且高效。</think>以下是为广告投放系统设计MySQL数据库的详细方案,我们将分步骤说明关字段设计思路: --- ### **基础结构** ```sql CREATE TABLE ads ( ## 主键与基础信息 ad_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '广告唯一标识', ad_name VARCHAR(255) NOT NULL COMMENT '广告名称', ad_content TEXT NOT NULL COMMENT '广告内容(文字/HTML)', media_url VARCHAR(512) COMMENT '素材地址(图片/视频)', ad_type ENUM('banner', 'video', 'text', 'native') NOT NULL DEFAULT 'banner' COMMENT '广告类型', ## 投放设置 start_time DATETIME NOT NULL COMMENT '投放开始时间', end_time DATETIME NOT NULL COMMENT '投放结束时间', status ENUM('active', 'paused', 'deleted') NOT NULL DEFAULT 'active' COMMENT '投放状态', ## 目标受众 target_conditions JSON COMMENT '定向条件(JSON结构存储)', ## 计费与预算 billing_type ENUM('CPC', 'CPM', 'CPA') NOT NULL COMMENT '计费类型', budget DECIMAL(15,2) UNSIGNED NOT NULL COMMENT '总预算', spent DECIMAL(15,2) UNSIGNED DEFAULT 0.00 COMMENT '已消耗金额', ## 统计指标 impressions BIGINT UNSIGNED DEFAULT 0 COMMENT '展示次数', clicks BIGINT UNSIGNED DEFAULT 0 COMMENT '点击次数', ## 关联信息 advertiser_id BIGINT UNSIGNED NOT NULL COMMENT '广告主ID', ## 系统记录 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间', ## 索引 INDEX idx_advertiser (advertiser_id), INDEX idx_time_range (start_time, end_time), INDEX idx_status (status), ## 约束 FOREIGN KEY (advertiser_id) REFERENCES advertisers(advertiser_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` --- ### **关字段说明** #### **1. 定向条件设计(target_conditions)** 使用JSON类型存储灵活定向规则: ```json { "geo": {"country": "CN", "province": ["北京","上海"]}, "device": {"type": ["mobile"], "os": "iOS"}, "language": "zh-CN", "interest_tags": [101, 205, 307], "exclude_audience": [8823, 9921] } ``` #### **2. 计费模式解释** $$ \text{CPC(单次点击成本)} = \frac{\text{总消耗}}{\text{点击次数}} $$ $$ \text{CPM(千次展示成本)} = \frac{\text{总消耗} \times 1000}{\text{展示次数}} $$ #### **3. 数据一致性保障** - 通过约束关联广告主 - 使用事务更新 `spent` 与统计字段 - 定时任务检查 `end_time` 并更新状态 --- ### **扩展建议** 1. **效果追踪**:单独创建 `ad_stats_daily` 记录每日数据 2. **频次控制**:添加 `frequency_cap` 字段限制用户单日观看次数 3. **AB测试**:增加 `experiment_group` 字段支持多版本测试 4. **审核系统**:添加 `review_status` 字段记录审核状态 --- ### **使用示例** ```sql -- 查询有效广告 SELECT * FROM ads WHERE status = 'active' AND start_time <= NOW() AND end_time >= NOW() AND budget > spent ORDER BY created_at DESC LIMIT 10; ``` 这个设计方案兼顾了灵活性(通过JSON字段)与查询效率(合理使用索引),建议根据具体业务需求调整字段约束索引策略。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值