New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL格式化不完善 #2347

Open
drtrang opened this Issue Feb 20, 2018 · 1 comment

Comments

Projects
None yet
2 participants
@drtrang
Copy link

drtrang commented Feb 20, 2018

问题

使用 com.alibaba.druid.sql.SQLUtils 格式化 SQL 时有格式异常的问题

复现

SQL

String sql = "SELECT id,task_id,task_source, housedel_code, del_type, office_address, company_code, brand, " +
                "class1_code, class2_code, class2_name, status, create_time, end_time, creator_ucid, creator_name, " +
                "org_code, prove_id, prove_time, audit_ucid, audit_name, audit_reject_reason, audit_content, " +
                "audit_time, pass_mode, sms_content, sms_time, lianjia_app_content, lianjia_app_time \r\n FROM " +
                "sh_true_house_task \r\n  WHERE office_address = 0 AND status = 0 ORDER     BY id DESC";

默认格式化结果

// 格式化代码
SQLUtils.formatMySql(sql)
// 输出结果
SELECT id, task_id, task_source, housedel_code, del_type
	, office_address, company_code, brand, class1_code, class2_code
	, class2_name, status, create_time, end_time, creator_ucid
	, creator_name, org_code, prove_id, prove_time, audit_ucid
	, audit_name, audit_reject_reason, audit_content, audit_time, pass_mode
	, sms_content, sms_time, lianjia_app_content, lianjia_app_time
FROM sh_true_house_task
WHERE office_address = 0
	AND status = 0
ORDER BY id DESC

仅大写格式化结果

// 格式化代码
SQLUtils.formatMySql(sql, new FormatOption(VisitorFeature.OutputUCase))
// 输出结果
SELECT id, task_id, task_source, housedel_code, del_type , office_address, company_code, brand, class1_code, class2_code , class2_name, status, create_time, end_time, creator_ucid , creator_name, org_code, prove_id, prove_time, audit_ucid , audit_name, audit_reject_reason, audit_content, audit_time, pass_mode , sms_content, sms_time, lianjia_app_content, lianjia_app_time FROM sh_true_house_task WHERE office_address = 0 AND status = 0 ORDER BY id DESC

由上述结果可以看到,在仅使用 OutputUCase 配置时,输出的 SQL 中 del_typeclass2_code 等字符后有多余的空格,望修复。

@wenshao wenshao added the Bug label Feb 22, 2018

@wenshao wenshao added this to the 1.1.9 milestone Feb 22, 2018

@drtrang

This comment has been minimized.

Copy link
Author

drtrang commented Feb 24, 2018

@wenshao

排查了一下发现问题最终出现在这里 SQLASTOutputVisitor.java

具体逻辑是每当查询的 column 数量为 5 的倍数时就调用 println() 方法

// SQLASTOutputVisitor.java#L386
if (lineItemCount >= selectListNumberOfLine) {
    lineItemCount = paramCount;
    println();
}

println() 的实现中,如果不开启 OutputPrettyFormat 特性时,就会添加一个空格

// SQLASTOutputVisitor.java#L440
public void println() {
    if (!isPrettyFormat()) {
        print(' ');
        return;
    }
}

请问这里添加空格的目的是什么?

@wenshao wenshao modified the milestones: 1.1.9, 1.1.10 Mar 11, 2018

@wenshao wenshao modified the milestones: 1.1.10, 1.1.11 May 27, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment