<think>根据错误信息，我们面临三个主要问题：1.重复添加列：在Users、Cart和Products表中重复添加了相同的列（FullName、PriceSnapshot、IsFeatured）2.主键冲突：插入Users数据时违反了主键约束，因为UserID=1已经存在3.订单创建逻辑在多次运行时可能产生重复数据解决方案：1.对于重复添加列的问题：在添加列之前检查列是否已存在2.对于主键冲突：在插入数据前检查是否已存在相同主键的记录3.对于订单创建：确保事务只执行一次，并且订单数据不会重复插入（根据业务逻辑可能需要调整）修改后的代码将：-使用`COL_LENGTH('表名','列名')ISNULL`检查列是否存在-使用`IFNOTEXISTS(SELECT...WHEREUserID=?)`避免重复插入用户-对于订单创建，考虑添加条件避免重复创建相同订单（这里根据业务需求，例如根据SessionID和UserID判断是否已存在）以下是完整的修正代码：</think>
根据错误信息，问题主要在于重复添加列和插入重复主键数据。以下是完全修正的代码：
```sql
-- 1. 添加缺失的表字段（带列存在检查）
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Users' AND type = 'U')
BEGIN
IF COL_LENGTH('Users', 'FullName') IS NULL
ALTER TABLE Users ADD FullName NVARCHAR(100) NOT NULL DEFAULT '';
IF COL_LENGTH('Users', 'IsAdmin') IS NULL
ALTER TABLE Users ADD IsAdmin BIT NOT NULL DEFAULT 0;
IF COL_LENGTH('Users', 'LastLogin') IS NULL
ALTER TABLE Users ADD LastLogin DATETIME NULL;
PRINT 'Users表字段添加/更新成功';
END
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Cart' AND type = 'U')
BEGIN
IF COL_LENGTH('Cart', 'PriceSnapshot') IS NULL
ALTER TABLE Cart ADD PriceSnapshot DECIMAL(10,2) NOT NULL DEFAULT 0;
PRINT 'Cart表字段添加/更新成功';
END
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Products' AND type = 'U')
BEGIN
IF COL_LENGTH('Products', 'IsFeatured') IS NULL
ALTER TABLE Products ADD IsFeatured BIT NOT NULL DEFAULT 0;
PRINT 'Products表字段添加/更新成功';
END
GO
-- 2. 修正插入用户数据（避免重复插入）
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Users' AND type = 'U')
BEGIN
-- 删除可能存在的重复数据
DELETE FROM Users WHERE UserID IN (1, 2, 3);
SET IDENTITY_INSERT Users ON;
INSERT INTO Users (UserID, Username, [Password], Email, FullName, AvatarUrl, IsAdmin, LastLogin)
VALUES
(1, 'sportsFan', 'e10adc3949ba59abbe56e057f20f883e', 'fan@example.com', '运动爱好者', '/images/user-avatar1.jpg', 0, '2023-11-15T08:30:00'),
(2, 'basketballPro', '25d55ad283aa400af464c76d713c07ad', 'pro@example.com', '篮球达人', '/images/user-avatar2.jpg', 0, '2023-11-18T14:20:00'),
(3, 'admin', '21232f297a57a5a743894a0e4a801fc3', 'admin@netshops.com', '系统管理员', '/images/admin-avatar.png', 1, '2023-11-20T09:15:00');
SET IDENTITY_INSERT Users OFF;
PRINT '用户数据插入成功';
END
GO
-- 3. 修正插入评论数据（避免重复）
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Reviews' AND type = 'U')
BEGIN
-- 删除可能的重复数据
DELETE FROM Reviews
WHERE (ProductID = 1 AND UserID = 1)
OR (ProductID = 2 AND UserID = 2)
OR (ProductID = 1 AND UserID = 2)
OR (ProductID = 3 AND UserID = 1)
OR (ProductID = 4 AND UserID = 3)
OR (ProductID = 8 AND UserID = 2)
OR (ProductID = 11 AND UserID = 1)
OR (ProductID = 12 AND UserID = 2);
INSERT INTO Reviews (ProductID, UserID, Rating, Comment, HelpfulCount, IsVerified)
VALUES
(1, 1, 5, '超级舒适的跑步鞋，减震效果一流！', 24, 1),
(2, 2, 4, '包裹性很好，但透气性一般', 8, 1),
(1, 2, 5, '最棒的篮球鞋，已经回购三次了', 42, 1),
(3, 1, 4, '背包设计很合理，就是有点小', 5, 0),
(4, 3, 5, '功能强大的手表，运动监测很精准', 15, 1),
(8, 2, 5, '欧文系列就是香，穿着舒适灵活', 37, 1),
(11, 1, 4, '经典复刻版，情怀满分', 28, 1),
(12, 2, 3, '材料质量没有想象中的好', 2, 0);
PRINT '评论数据插入成功';
END
GO
-- 4. 修正插入购物车数据（避免重复）
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Cart' AND type = 'U')
BEGIN
-- 删除可能的重复数据
DELETE FROM Cart
WHERE (UserID = 1 AND SessionID = 'session_12345' AND ProductID = 1)
OR (UserID = 1 AND SessionID = 'session_12345' AND ProductID = 4)
OR (UserID = 1 AND SessionID = 'session_12345' AND ProductID = 8)
OR (UserID IS NULL AND SessionID = 'guest_session' AND ProductID = 5)
OR (UserID IS NULL AND SessionID = 'guest_session' AND ProductID = 11);
INSERT INTO Cart (UserID, SessionID, ProductID, Quantity, PriceSnapshot)
VALUES
(1, 'session_12345', 1, 1, 628.00),
(1, 'session_12345', 4, 1, 5339.00),
(1, 'session_12345', 8, 2, 3299.00),
(NULL, 'guest_session', 5, 1, 429.00),
(NULL, 'guest_session', 11, 1, 2689.00);
PRINT '购物车数据插入成功';
END
GO
-- 5. 安全订单处理（带重复订单检查）
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Orders' AND type = 'U')
AND EXISTS (SELECT * FROM sys.tables WHERE name = 'OrderDetails' AND type = 'U')
BEGIN
BEGIN TRY
-- 检查是否已存在相同订单
IF NOT EXISTS (
SELECT 1
FROM Orders
WHERE UserID = 1
AND SessionID = 'completed_session_123'
AND TotalAmount = 8967.00
)
BEGIN
BEGIN TRANSACTION;
DECLARE @OrderID INT;
-- 插入订单
INSERT INTO Orders (UserID, SessionID, TotalAmount, Status, ShippingAddress, PaymentMethod)
VALUES
(1, 'completed_session_123', 8967.00, 'Delivered', '北京市朝阳区朝阳公园路8号', 'WeChat Pay');
SET @OrderID = SCOPE_IDENTITY();
-- 插入订单详情
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice, Subtotal)
VALUES
(@OrderID, 1, 1, 628.00, 628.00),
(@OrderID, 4, 1, 5339.00, 5339.00),
(@OrderID, 8, 2, 3299.00, 6598.00);
COMMIT TRANSACTION;
PRINT '订单创建成功，订单号: ' + CAST(@OrderID AS NVARCHAR);
END
ELSE
BEGIN
PRINT '订单已存在，跳过创建';
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT '订单处理错误: ' + ERROR_MESSAGE();
END CATCH
END
GO
-- 6. 安全更新推荐商品状态（避免重复执行）
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Products' AND type = 'U')
BEGIN
-- 更新指定商品为推荐状态
UPDATE Products
SET IsFeatured = 1
WHERE ProductID IN (2, 4, 8, 11);
PRINT '推荐商品更新成功';
END
GO
```
### 主要修正内容：
1. **列存在检查**：
```sql
IF COL_LENGTH('TableName', 'ColumnName') IS NULL
```
避免重复添加已存在的列
2. **数据存在检查**：
```sql
-- 用户数据
DELETE FROM Users WHERE UserID IN (1, 2, 3);
-- 订单数据
IF NOT EXISTS (SELECT 1 FROM Orders WHERE ...)
```
3. **更精确的重复检查**：
```sql
-- 购物车重复检查
WHERE (UserID = 1 AND SessionID = 'session_12345' AND ProductID = 1)
```
4. **安全的事务处理**：
```sql
BEGIN TRY
BEGIN TRANSACTION;
-- 操作
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCH
```
5. **多表联合检查**：
```sql
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Orders')
AND EXISTS (SELECT * FROM sys.tables WHERE name = 'OrderDetails')
```
### 最佳实践增强：
1. **使用 COL_LENGTH 替代 sys.columns**：
```sql
-- 性能更好的列存在检查
IF COL_LENGTH('Users', 'FullName') IS NULL
```
2. **精确的 WHERE 子句**：
```sql
-- 避免全表删除
DELETE FROM Reviews
WHERE (ProductID = 1 AND UserID = 1)
OR ...
```
3. **安全的事务状态检查**：
```sql
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
```
4. **详细的状态反馈**：
```sql
PRINT '订单创建成功，订单号: ' + CAST(@OrderID AS NVARCHAR);
PRINT '订单已存在，跳过创建';
```
###
扫一扫
1481
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
