如何从SQL Server按百分比导出数据？需完整Schema及30%数据用于开发

针对不同数据库，这里提供实用的分步方案，既能保留完整Schema，又能抽取约30%的样本数据：

MySQL #

1. 导出全量Schema（不含数据）
   使用mysqldump命令仅导出结构：

mysqldump -u [你的用户名] -p --no-data [目标数据库名] > schema.sql

输入密码后即可生成仅含表结构的SQL文件。

2. 抽取30%样本数据

• 单表抽样：直接用随机排序取数，适合小表：

SELECT * FROM [表名] ORDER BY RAND() LIMIT 30%;

• 批量处理所有表：用Shell脚本自动遍历并导出每个表的样本，效率更高：

# 获取数据库所有表名
tables=$(mysql -u [你的用户名] -p[你的密码] -N -e "SHOW TABLES FROM [目标数据库名]")

# 循环导出每个表的30%数据
for table in $tables; do
  # 用WHERE子句实现抽样，替代LIMIT更灵活
  mysqldump -u [你的用户名] -p[你的密码] [目标数据库名] $table --where="FLOOR(RAND() * 100) < 30" >> data_sample.sql
done

注：FLOOR(RAND() * 100) < 30比ORDER BY RAND() LIMIT效率更高，适合大表，随机性虽稍弱但足够满足开发需求。

3. 合并导入到开发库
   先导入Schema，再导入样本数据：

mysql -u [开发库用户名] -p [开发库名] < schema.sql && mysql -u [开发库用户名] -p [开发库名] < data_sample.sql

PostgreSQL #

1. 导出全量Schema
   用pg_dump导出仅结构的文件：

pg_dump -U [你的用户名] -s [目标数据库名] > schema.sql

2. 抽取30%样本数据
   PostgreSQL支持TABLESAMPLE语法，抽样效率极高：

• 单表抽样：

SELECT * FROM [表名] TABLESAMPLE SYSTEM(30);

• 批量处理所有表：

# 获取public schema下的所有表名
tables=$(psql -U [你的用户名] -d [目标数据库名] -t -c "SELECT tablename FROM pg_tables WHERE schemaname='public';")

for table in $tables; do
  pg_dump -U [你的用户名] -d [目标数据库名] -t "$table" --data-only --table-sample=30% >> data_sample.sql
done

3. 合并导入

psql -U [开发库用户名] -d [开发库名] -f schema.sql && psql -U [开发库用户名] -d [开发库名] -f data_sample.sql

SQL Server #

1. 导出全量Schema
   用sqlcmd调用系统存储过程生成Schema脚本：

sqlcmd -S [服务器地址] -U [你的用户名] -P [你的密码] -d [目标数据库名] -Q "EXEC sp_generate_script @database_name='[目标数据库名]', @type='schema'" -o schema.sql

也可以通过SSMS可视化操作：右键数据库→任务→生成脚本→仅选择Schema。

2. 抽取30%样本数据
   用TOP 30 PERCENT结合随机排序实现抽样：

• 单表抽样：

SELECT TOP 30 PERCENT * FROM [表名] ORDER BY NEWID();

• 批量处理：用PowerShell遍历所有表导出数据：

# 获取所有表名
$tables = Invoke-SqlCmd -ServerInstance "[服务器地址]" -Database "[目标数据库名]" -Query "SELECT name FROM sys.tables;"

foreach ($table in $tables) {
  # 导出样本数据到文件
  bcp "[目标数据库名].[dbo].[$($table.name)]" out "C:\temp\$($table.name)_sample.dat" -S [服务器地址] -U [你的用户名] -P [你的密码] -q -c -t "," -Q "SELECT TOP 30 PERCENT * FROM [目标数据库名].[dbo].[$($table.name)] ORDER BY NEWID()"
}

3. 合并导入
   先执行schema.sql创建结构，再用bcp将每个数据文件导入对应表。

关键注意事项 #

• 关联表一致性：如果存在外键关联（如订单表关联用户表），不能单独随机抽样。需先抽取主表（如用户表）的30%数据，再根据主表的ID集合抽取关联表的对应数据，避免数据引用错误。
• 特殊表全量导出：配置表、字典表等数据量小但业务关键的表，建议直接全量导出，抽样可能导致开发环境功能异常。
• 大表性能优化：超大型表避免用RAND()/NEWID()，可改用主键取模（如WHERE id % 3 = 0，约33%样本）或按主键范围分段抽取，大幅提升效率。

内容的提问来源于stack exchange，提问作者S.A.Parkhid