创建临时表 tmp_count_control,用于辅助数据的插入
create table tmp_count_control(id int);
insert into table tmp_count_control values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16);
select * from tmp_count_control;
插入数据到
insert into mall_data.dtw_mall2_adm_customer_d_tmp partition(ds='20180615', tenantid=1, mallname='big')
select
id,cast(rand()*1000000 as int)+1000000,
cast(rand()*1000000 as int)+1000000,
concat(
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)],
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)],
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)],
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)],
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)],
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)],
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)]
),
split('m f notclear',' ')[cast((rand()*3) as int)],
cast((rand()*40) as int)+10,
13344455666,
'dtw123@dtw-inc.com',
concat(concat(199,split('0 1 2 3 4 5 6 7 8 9',' ')[cast(rand()*10 as int)]),
split('01 02 03 04 05 06 07 08 09 10 11 12',' ')[cast(rand()*12 as int)],
concat(split('0 1 2',' ')[cast(rand()*3 as int)],split('1 2 3 4 5 6 7 8',' ')[cast(rand()*8 as int)])
),
split('北京 上海 广州 深圳 杭州 厦门 福州 济南 青岛 烟台 济宁 临沂 莱芜 曲阜 平壤 太原 阜阳 合肥 嵊州 平度 拉萨 昆明 沈阳 长春 哈尔滨',' ')[cast(rand()*25 as int)],
split('有 无 不确定',' ')[cast((rand()*3) as int)],
cast(rand()*1000000 as int)+1000000,
cast(rand()*100000 as int),
split('未激活 已激活',' ')[cast(rand()*2 as int)],
concat(concat(201,split('0 1 2 3 4 5 6 7',' ')[cast(rand()*8 as int)]),
split('01 02 03 04 05 06 07 08 09 10 11 12',' ')[cast(rand()*12 as int)],
concat(split('0 1 2',' ')[cast(rand()*3 as int)],split('1 2 3 4 5 6 7 8',' ')[cast(rand()*8 as int)])
),
split('到访客户 成交客户 会员客户',' ')[cast(rand()*3 as int)],
split('新客 活跃客户 忠诚客户 流失客户 回头客',' ')[cast(rand()*5 as int)],
split('新客户 老客户',' ')[cast(rand()*2 as int)],
split('初级访客 中级访客 高级访客 初级会员 中级会员 高级会员',' ')[cast(rand()*6 as int)],
split('低 中 高',' ')[cast(rand()*3 as int)],
concat(
'{',
split('花卉一族 摄影一族 健美一族 果粉 电影派 阅读者 舞林人士 乐器迷 爱听音乐 童心未泯',' ')[cast(rand()*10 as int)],
',',
split('御宅族 白富美 高帅富 呼朋唤友 休闲大咖 装修一族 甜蜜新人 户外一族 网络一族 酒品人生',' ')[cast(rand()*10 as int)],
',',
split('有型潮男 拜金主义 数码达人 家有儿女 养生专家 运动一族 车友派 家有宠物 游戏人生 爱家人士',' ')[cast(rand()*10 as int)],
'}'
),
concat(
'{',
split('明确果断 犹豫再三 喜新厌旧 货比三家',' ')[cast(rand()*4 as int)],
',',
split('只逛不买 虚荣型 忠诚型',' ')[cast(rand()*3 as int)],
',',
split('挑剔型 将就型 跟风型',' ')[cast(rand()*3 as int)],
'}'
),
split('川菜 粤菜 淮扬菜 浙菜 湘菜 徽菜 闽菜 日本菜 台湾菜 韩国菜',' ')[cast(rand()*10 as int)],
split('低 中 高',' ')[cast(rand()*3 as int)],
split('油 清淡 辣 麻 咸',' ')[cast(rand()*5 as int)],
split('上午 中午 下午 黄昏 傍晚 晚上',' ')[cast(rand()*6 as int)],
split('中餐 西餐 自助餐 小吃快餐 饮品店 面包甜点 火锅 特色菜 烧烤',' ')[cast(rand()*9 as int)],
cast(rand()*50 as int),
round(rand()*50,1)+5,
round(rand()*5,1),
cast(rand()*10 as int),
round(rand()*5,1)+1,
concat(concat(201,split('6 7 8',' ')[cast(rand()*3 as int)]),
split('01 02 03 04 05 06 07 08 09 10 11 12',' ')[cast(rand()*12 as int)],
concat(split('0 1 2',' ')[cast(rand()*3 as int)],split('1 2 3 4 5 6 7 8',' ')[cast(rand()*8 as int)])
),
round(rand()*5000,1)+5000,
cast(rand()*50 as int),
round(rand()*1000,1)+500,
cast(rand()*20 as int),
round(rand()*500,1)+50,
cast(rand()*20 as int),
round(rand()*100,1)+50,
concat(concat(201,split('6 7 8',' ')[cast(rand()*3 as int)]),
split('01 02 03 04 05 06 07 08 09 10 11 12',' ')[cast(rand()*12 as int)],
concat(split('0 1 2',' ')[cast(rand()*3 as int)],split('1 2 3 4 5 6 7 8',' ')[cast(rand()*8 as int)])
)
from tmp_count_control [limit 5000];
OK，完成数据的插入！！！
通过新建临时表来辅助实现插入数据条数的控制，否则就得通过insert into tablename values (******),(******)......形式，写多次上面蓝色的sql（上面80行sql仅仅插入1条数据）
浙公网安备 33010602011771号