这样的目的是为了后续方便说说附带的图片进行修改以及调用三方接口校验图片是否违规。

大概表设计如下：

622ed5483de15a276879cab129b21cb

怎么转移说说表的pics字段的****url 到新的y_talk_images字段呢？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INSERT INTO y_talk_images (talk_bid, images_url, user_bid, trace_id, is_valid, is_delete, create_time)
SELECT
    bid,
    SUBSTRING_INDEX(SUBSTRING_INDEX(pics, ',', numbers.n), ',', -1) AS images_url,
    y_talk.user_bid,
    NULL AS trace_id,
    1 AS is_valid,
    0 AS is_delete,
    y_talk.create_time -- 使用此图片对应的说说创建时间作为创建时间
FROM
    (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
     UNION ALL SELECT 9) numbers
JOIN y_talk ON CHAR_LENGTH(pics) - CHAR_LENGTH(REPLACE(pics, ',', '')) >= numbers.n - 1
WHERE
    pics IS NOT NULL AND pics != '';
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE y_talk_images (
	id INT ( 11 ) NOT NULL AUTO_INCREMENT,
	image_bid VARCHAR ( 255 ) COMMENT '图片的主键id',
	talk_bid VARCHAR ( 255 ) COMMENT '此图片对应的说说的主键id',
	images_url VARCHAR ( 1024 ) COMMENT '图片的url',
	user_bid VARCHAR ( 255 ) COMMENT '发布者的主键id',
	open_id VARCHAR ( 255 ) DEFAULT NULL COMMENT '微信id',
	trace_id VARCHAR ( 50 ) COMMENT '每张照片微信接口校验时返回的任务id',
	is_valid INT ( 1 ) NOT NULL DEFAULT 0 COMMENT '是否通过校验，0待审核，1审核通过，2审核不通过',
	is_delete INT ( 1 ) NOT NULL DEFAULT 0 COMMENT '图片url是否被软删除',
	create_time datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ( `id` ) USING BTREE 
);