mysql 按照条件导出表sql

[login]

```
using MySqlConnector;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Security.Policy;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace exportSql
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

private void button1_Click(object sender, EventArgs e)
        {
            //读取文件
            FileStream fs = new FileStream(@"D:\TableNames.txt", FileMode.Open, FileAccess.Read);
            StreamReader sr = new StreamReader(fs);
            string str = sr.ReadToEnd();
            string[] tableNames = str.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
            foreach (string tableName in tableNames)
            {
                Thread.Sleep(1000);
                if (tableName.Equals("v_facilities")) {

}
                else
                {
                  
                    exportSql(tableName);
                }
                
            }
            //exportSql("basics_tank");

}

private void viewsTable()
        {

string v_facilities = "CREATE VIEW `v_facilities` AS select 1003 AS `type`,`t`.`id` AS `id`,`t`.`tank_name` AS `name`,`t`.`risk_rating_code` AS `risk_rating_code`,`t`.`fire_hazard_code` AS `fire_hazard_code`,`t`.`org_code` AS `org_code`,`t`.`department` AS `department`,`te`.`short_name` AS `org_name`,`t`.`danger_id` AS `danger_id`,`td`.`name` AS `danger_name`,`td`.`level_code` AS `level_code`,`td`.`hazard_facility` AS `hazard_facility`,`t`.`tank_area_id` AS `tank_area_id`,`ta`.`tank_area_name` AS `tank_area_name`,`t`.`tenant_id` AS `tenant_id` from (((`basics_tank` `t` left join `enterprise_info` `te` on((`t`.`org_code` = `te`.`org_code`))) left join `basics_tank_area` `ta` on((`t`.`tank_area_id` = `ta`.`id`))) left join `basics_danger` `td` on((`td`.`id` = `t`.`danger_id`))) where (`t`.`del_flag` = '0') union all select 1007 AS `type`,`t`.`id` AS `id`,`t`.`name` AS `name`,`t`.`risk_rating_code` AS `risk_rating_code`,`t`.`fire_hazard_code` AS `fire_hazard_code`,`t`.`org_code` AS `org_code`,`t`.`department` AS `department`,`te`.`short_name` AS `org_name`,`t`.`danger_id` AS `danger_id`,`td`.`name` AS `danger_name`,`td`.`level_code` AS `level_code`,`td`.`hazard_facility` AS `hazard_facility`,NULL AS `tank_area_id`,NULL AS `tank_area_name`,`t`.`tenant_id` AS `tenant_id` from ((`basics_produce_unit` `t` left join `enterprise_info` `te` on((`t`.`org_code` = `te`.`org_code`))) left join `basics_danger` `td` on((`td`.`id` = `t`.`danger_id`))) where (`t`.`del_flag` = '0') union all select 1005 AS `type`,`t`.`id` AS `id`,`t`.`warehouse_name` AS `name`,`t`.`risk_rating_code` AS `risk_rating_code`,`t`.`fire_hazard_code` AS `fire_hazard_code`,`t`.`org_code` AS `org_code`,`t`.`department` AS `department`,`te`.`short_name` AS `org_name`,`t`.`danger_id` AS `danger_id`,`td`.`name` AS `danger_name`,`td`.`level_code` AS `level_code`,`td`.`hazard_facility` AS `hazard_facility`,NULL AS `tank_area_id`,NULL AS `tank_area_name`,`t`.`tenant_id` AS `tenant_id` from ((`basics_warehouse` `t` left join `enterprise_info` `te` on((`t`.`org_code` = `te`.`org_code`))) left join `basics_danger` `td` on((`td`.`id` = `t`.`danger_id`))) where (`t`.`del_flag` = '0') union all select 1008 AS `type`,`t`.`id` AS `id`,`t`.`name` AS `name`,NULL AS `risk_rating_code`,NULL AS `fire_hazard_code`,`t`.`org_code` AS `org_code`,`t`.`department` AS `department`,`te`.`short_name` AS `org_name`,NULL AS `danger_id`,NULL AS `danger_name`,NULL AS `level_code`,NULL AS `hazard_facility`,NULL AS `tank_area_id`,NULL AS `tank_area_name`,`t`.`tenant_id` AS `tenant_id` from (`basics_equipment` `t` left join `enterprise_info` `te` on((`t`.`org_code` = `te`.`org_code`))) where (`t`.`del_flag` = '0') union all select 1002 AS `type`,`t`.`id` AS `id`,`t`.`tank_area_name` AS `name`,`t`.`risk_rating_code` AS `risk_rating_code`,`t`.`fire_hazard_code` AS `fire_hazard_code`,`t`.`org_code` AS `org_code`,`t`.`department` AS `department`,`te`.`short_name` AS `org_name`,`t`.`danger_id` AS `danger_id`,`td`.`name` AS `danger_name`,`td`.`level_code` AS `level_code`,`td`.`hazard_facility` AS `hazard_facility`,NULL AS `tank_area_id`,NULL AS `tank_area_name`,`t`.`tenant_id` AS `tenant_id` from ((`basics_tank_area` `t` left join `enterprise_info` `te` on((`t`.`org_code` = `te`.`org_code`))) left join `basics_danger` `td` on((`td`.`id` = `t`.`danger_id`))) where (`t`.`del_flag` = '0') union all select 1004 AS `type`,`t`.`id` AS `id`,`t`.`name` AS `name`,`t`.`risk_rating_code` AS `risk_rating_code`,`t`.`fire_hazard_code` AS `fire_hazard_code`,`t`.`org_code` AS `org_code`,`t`.`department` AS `department`,`te`.`short_name` AS `org_name`,`t`.`danger_id` AS `danger_id`,`td`.`name` AS `danger_name`,`td`.`level_code` AS `level_code`,`td`.`hazard_facility` AS `hazard_facility`,NULL AS `tank_area_id`,NULL AS `tank_area_name`,`t`.`tenant_id` AS `tenant_id` from ((`basics_warehouse_area` `t` left join `enterprise_info` `te` on((`t`.`org_code` = `te`.`org_code`))) left join `basics_danger` `td` on((`td`.`id` = `t`.`danger_id`))) where (`t`.`del_flag` = '0');";
            
            //写到文件
            FileStream fs = new FileStream(@"D:\sql\v_facilities.sql", FileMode.Create, FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs);
            sw.Write(v_facilities);
            sw.Flush();
            sw.Close();
            fs.Close();
        }

private void exportSql(string tableName)
        {
            viewsTable();
            // MySQL 连接字符串
            string connectionString = "server="+ textBox_mysqlIp.Text +";port="+textBox_mysqlPort.Text+";user="+ textBox_mysqlUser.Text +";password="+textBox_mysqlPwd.Text+";database=jeecg-boot;charset=utf8";

string iswhere = "";
            // 检查表中是否存在 tenant_id 字段
            int itis = IsColumnExists2(connectionString, tableName);
            if (itis==3)
            {
                Console.WriteLine($"表 {tableName} 中不存在 rel_tenant_ids tenant_id 字段，全部导出数据。");
                textBox2.AppendText($"表 {tableName} 中不存在 rel_tenant_ids tenant_id 字段，全部导出数据。\r\n");
                
            }
            else if(itis==1){
                Console.WriteLine($"表 {tableName} 中存在 tenant_id 字段，根据 tenant_id 进行数据导出。");
                textBox2.AppendText($"表 {tableName} 中存在 tenant_id 字段，根据 tenant_id 进行数据导出。");
                iswhere = "--where=\""+ "tenant_id="+ textBox_tenant_id.Text + " or tenant_id=1" + "\"";
            }else if (itis == 2)
            {
                Console.WriteLine($"表 {tableName} 中存在 rel_tenant_ids 字段，根据 rel_tenant_ids 进行数据导出。");
                textBox2.AppendText($"表 {tableName} 中存在 rel_tenant_ids 字段，根据 rel_tenant_ids 进行数据导出。");
                iswhere = "--where=\"" + "rel_tenant_ids=" + textBox_tenant_id.Text + " or rel_tenant_ids=1" + "\"";
            }
            //
            if (tableName.Equals("alarm_indicator_recording") || tableName.Equals("alarm_behavioral_analysis"))
            {
                iswhere = "--where=\"id=1\"";
            }

string sql = "mysqldump --quick --host=" + textBox_mysqlIp.Text + " --default-character-set=utf8 --lock-tables --verbose " + iswhere + " --force --port=" + textBox_mysqlPort.Text + " --user=" + textBox_mysqlUser.Text + " --password=" + textBox_mysqlPwd.Text + " jeecg-boot " + tableName + " -r \"{0}\"";

StringBuilder commandinfo = new StringBuilder();
            string directory = @"D:\sql\" + tableName + ".sql"; // 导出 SQL 文件存储的路径
            commandinfo.AppendFormat(sql, directory); 
            String exportCommand = commandinfo.ToString();
            // 输出到控制台
            Console.WriteLine(exportCommand);
            textBox2.AppendText(exportCommand + "\r\n");

// 获取 mysqldump.exe 所在路径
            String mysqldumpDirecroty = textBox_mysqlBin.Text;

Process p = new Process();
            p.StartInfo.FileName = "cmd.exe";
            p.StartInfo.WorkingDirectory = mysqldumpDirecroty;
            p.StartInfo.UseShellExecute = false;
            p.StartInfo.RedirectStandardInput = true;
            p.StartInfo.RedirectStandardOutput = true;
            p.StartInfo.RedirectStandardError = true;
            p.StartInfo.CreateNoWindow = true;
            p.Start();
            p.StandardInput.WriteLine(exportCommand);
            p.StandardInput.WriteLine("exit");
        }

private bool IsColumnExists(string connectionString, string tableName, string columnName)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                string query = $"SHOW COLUMNS FROM `{tableName}` LIKE '{columnName}';";
                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        return reader.HasRows;
                    }
                }
            }
        }

private int IsColumnExists2(string connectionString, string tableName)
        {
            string columnName1 = "tenant_id";
            string columnName2 = "rel_tenant_ids";

using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

// 检查 columnName1 是否存在
                string query1 = $"SHOW COLUMNS FROM `{tableName}` LIKE '{columnName1}';";
                using (MySqlCommand command = new MySqlCommand(query1, connection))
                {
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            return 1; // columnName1 存在
                        }
                    }
                }

// 检查 columnName2 是否存在
                string query2 = $"SHOW COLUMNS FROM `{tableName}` LIKE '{columnName2}';";
                using (MySqlCommand command = new MySqlCommand(query2, connection))
                {
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            return 2; // columnName2 存在
                        }
                    }
                }
            }

return 3; // 两个列名都不存在
        }

private string mysqlIp;
        private string mysqlPort;
        private string mysqlUser;
        private string mysqlPassword;
        private string tenantId;
        private string mysqlBin;

private void Form1_Load(object sender, EventArgs e)
        {
            //输出资源里的TableNames 到文件
           // string resourceName =

//读配置文件 运行目录 config.json
            string path = AppDomain.CurrentDomain.BaseDirectory + "config.json";
            string json = null;
            try
            {
                json = File.ReadAllText(path);
            }
            catch (Exception ex)
            {

}
            if (json == null)
            {
                mysqlIp = @"127.0.0.1";
                mysqlPort = @"3306";
                mysqlUser = @"root";
                mysqlPassword = @"admin@2023";
                tenantId = "1";
                mysqlBin = @"D:\devsoft\mysql-5.7.44-winx64\bin";

textBox_tenant_id.Text = tenantId;
                textBox_mysqlIp.Text = mysqlIp;
                textBox_mysqlPort.Text = mysqlPort;
                textBox_mysqlUser.Text = mysqlUser;
                textBox_mysqlPwd.Text = mysqlPassword;
                textBox_mysqlBin.Text = mysqlBin;

//保存配置文件
                dynamic obj = new {mysqlIp= mysqlIp, mysqlPort = mysqlPort, mysqlUser = mysqlUser, mysqlPassword = mysqlPassword ,mysqlBin=mysqlBin};
                string jsonStr = JsonConvert.SerializeObject(obj);
                File.WriteAllText(path, jsonStr);

}
            else
            {
                dynamic obj = JsonConvert.DeserializeObject(json);
                mysqlIp = obj.mysqlIp;
                mysqlPort = obj.mysqlPort;
                mysqlUser = obj.mysqlUser;
                mysqlPassword = obj.mysqlPassword;
                tenantId = obj.tenantId;
                mysqlBin = obj.mysqlBin;

}
        }

private void button2_Click(object sender, EventArgs e)
        {
            mysqlIp = textBox_mysqlIp.Text;
            mysqlPort = textBox_mysqlPort.Text;
            mysqlUser = textBox_mysqlUser.Text;
            mysqlPassword = textBox_mysqlPwd.Text;
            tenantId = textBox_tenant_id.Text;
            mysqlBin = textBox_mysqlBin.Text;
            string path = AppDomain.CurrentDomain.BaseDirectory + "config.json";
            string json = File.ReadAllText(path);
            dynamic obj = JsonConvert.DeserializeObject(json);
            obj.mysqlIp = mysqlIp;
            obj.mysqlPort = mysqlPort;
            obj.mysqlUser = mysqlUser;
            obj.mysqlPassword = mysqlPassword;
            obj.tenantId = tenantId;
            obj.mysqlBin = mysqlBin;
            string jsonStr = JsonConvert.SerializeObject(obj);
            File.WriteAllText(path, jsonStr);
        }

}
}

```

[/login]

该部分仅登录用户可见

最后修改：2024 年 07 月 26 日

如果觉得我的文章对你有用，请随意赞赏

mysql 按照条件导出表sql

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

[2017-12-10测试]BiliBili,B站,哔哩哔哩免流失效

燕尾蝶&蓝闪蝶抢购脚本(完整版带微信提示自动提交)

Flarum 自定义样式html

随机言php,数据库格式,提取随机言数据库

Flarum 伪静态重写规则

openwrt插件

自用脚本

ContOs6下开启MySql远程

nginx加密访问网页

grpc trojan vl vm配置

mysql 按照条件导出表sql

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

mysql 按照条件导出表sql

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款