You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by ch...@apache.org on 2026/04/30 06:04:08 UTC

(shardingsphere) branch master updated: Refactor EncryptAlterTableTokenGenerator and EncryptCreateTableTokenGenerator logic (#38661)

This is an automated email from the ASF dual-hosted git repository.

FlyingZC pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git


The following commit(s) were added to refs/heads/master by this push:
     new fe04c30d7eb Refactor EncryptAlterTableTokenGenerator and EncryptCreateTableTokenGenerator logic (#38661)
fe04c30d7eb is described below

commit fe04c30d7eb7b2308820fe0909201b5fc3be763a
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Thu Apr 30 14:04:00 2026 +0800

    Refactor EncryptAlterTableTokenGenerator and EncryptCreateTableTokenGenerator logic (#38661)
    
    * Refactor EncryptAlterTableTokenGenerator and EncryptCreateTableTokenGenerator logic
    
    * Fix unit test
---
 ...ableOption.java => DialectAddColumnOption.java} |   8 +-
 .../option/altertable/DialectAlterTableOption.java |  15 +++
 .../metadata/database/OracleDatabaseMetaData.java  |   3 +-
 .../database/OracleDatabaseMetaDataTest.java       |   1 +
 .../database/SQLServerDatabaseMetaData.java        |   3 +-
 .../database/SQLServerDatabaseMetaDataTest.java    |   3 +-
 .../ddl/EncryptAlterTableTokenGenerator.java       | 126 ++++++++++++++++++---
 .../ddl/EncryptCreateTableTokenGenerator.java      |   6 +-
 .../ddl/EncryptAlterTableTokenGeneratorTest.java   |  20 ++--
 .../query-with-cipher/ddl/alter/alter-table.xml    |   2 +-
 10 files changed, 150 insertions(+), 37 deletions(-)

diff --git a/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/altertable/DialectAlterTableOption.java b/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/altertable/DialectAddColumnOption.java
similarity index 84%
copy from database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/altertable/DialectAlterTableOption.java
copy to database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/altertable/DialectAddColumnOption.java
index 3e166815e9c..efd36dda580 100644
--- a/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/altertable/DialectAlterTableOption.java
+++ b/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/altertable/DialectAddColumnOption.java
@@ -21,13 +21,13 @@ import lombok.Getter;
 import lombok.RequiredArgsConstructor;
 
 /**
- * Dialect alter table option.
+ * Dialect add column option.
  */
 @RequiredArgsConstructor
 @Getter
-public final class DialectAlterTableOption {
+public final class DialectAddColumnOption {
     
-    private final boolean isSupportMergeDropColumns;
+    private final String beforeAllAddColumns;
     
-    private final boolean containsParenthesesOnMergeDropColumns;
+    private final String beforeEachAddColumn;
 }
diff --git a/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/altertable/DialectAlterTableOption.java b/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/altertable/DialectAlterTableOption.java
index 3e166815e9c..93a39ca30ac 100644
--- a/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/altertable/DialectAlterTableOption.java
+++ b/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/database/metadata/option/altertable/DialectAlterTableOption.java
@@ -20,6 +20,8 @@ package org.apache.shardingsphere.database.connector.core.metadata.database.meta
 import lombok.Getter;
 import lombok.RequiredArgsConstructor;
 
+import java.util.Optional;
+
 /**
  * Dialect alter table option.
  */
@@ -30,4 +32,17 @@ public final class DialectAlterTableOption {
     private final boolean isSupportMergeDropColumns;
     
     private final boolean containsParenthesesOnMergeDropColumns;
+    
+    private final boolean containsParenthesesOnColumnsClause;
+    
+    private final DialectAddColumnOption addColumnOption;
+    
+    /**
+     * Get add column option.
+     *
+     * @return add column option
+     */
+    public Optional<DialectAddColumnOption> getAddColumnOption() {
+        return Optional.ofNullable(addColumnOption);
+    }
 }
diff --git a/database/connector/dialect/oracle/src/main/java/org/apache/shardingsphere/database/connector/oracle/metadata/database/OracleDatabaseMetaData.java b/database/connector/dialect/oracle/src/main/java/org/apache/shardingsphere/database/connector/oracle/metadata/database/OracleDatabaseMetaData.java
index 00ff73e44b3..441e08c96b7 100644
--- a/database/connector/dialect/oracle/src/main/java/org/apache/shardingsphere/database/connector/oracle/metadata/database/OracleDatabaseMetaData.java
+++ b/database/connector/dialect/oracle/src/main/java/org/apache/shardingsphere/database/connector/oracle/metadata/database/OracleDatabaseMetaData.java
@@ -21,6 +21,7 @@ import org.apache.shardingsphere.database.connector.core.metadata.database.enums
 import org.apache.shardingsphere.database.connector.core.metadata.database.enums.QuoteCharacter;
 import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.DialectDatabaseMetaData;
 import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.option.IdentifierPatternType;
+import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.option.altertable.DialectAddColumnOption;
 import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.option.altertable.DialectAlterTableOption;
 import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.option.connection.DialectConnectionOption;
 import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.option.datatype.DialectDataTypeOption;
@@ -89,7 +90,7 @@ public final class OracleDatabaseMetaData implements DialectDatabaseMetaData {
     
     @Override
     public Optional<DialectAlterTableOption> getAlterTableOption() {
-        return Optional.of(new DialectAlterTableOption(true, true));
+        return Optional.of(new DialectAlterTableOption(true, true, true, new DialectAddColumnOption("ADD", "")));
     }
     
     @Override
diff --git a/database/connector/dialect/oracle/src/test/java/org/apache/shardingsphere/database/connector/oracle/metadata/database/OracleDatabaseMetaDataTest.java b/database/connector/dialect/oracle/src/test/java/org/apache/shardingsphere/database/connector/oracle/metadata/database/OracleDatabaseMetaDataTest.java
index 7964b77a106..f57ff786ec8 100644
--- a/database/connector/dialect/oracle/src/test/java/org/apache/shardingsphere/database/connector/oracle/metadata/database/OracleDatabaseMetaDataTest.java
+++ b/database/connector/dialect/oracle/src/test/java/org/apache/shardingsphere/database/connector/oracle/metadata/database/OracleDatabaseMetaDataTest.java
@@ -112,6 +112,7 @@ class OracleDatabaseMetaDataTest {
         assertTrue(actualAlterTableOption.isPresent());
         assertTrue(actualAlterTableOption.get().isSupportMergeDropColumns());
         assertTrue(actualAlterTableOption.get().isContainsParenthesesOnMergeDropColumns());
+        assertTrue(actualAlterTableOption.get().isContainsParenthesesOnColumnsClause());
     }
     
     @Test
diff --git a/database/connector/dialect/sqlserver/src/main/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/SQLServerDatabaseMetaData.java b/database/connector/dialect/sqlserver/src/main/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/SQLServerDatabaseMetaData.java
index e528545bb96..fd4c0370ff9 100644
--- a/database/connector/dialect/sqlserver/src/main/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/SQLServerDatabaseMetaData.java
+++ b/database/connector/dialect/sqlserver/src/main/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/SQLServerDatabaseMetaData.java
@@ -21,6 +21,7 @@ import org.apache.shardingsphere.database.connector.core.metadata.database.enums
 import org.apache.shardingsphere.database.connector.core.metadata.database.enums.QuoteCharacter;
 import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.DialectDatabaseMetaData;
 import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.option.IdentifierPatternType;
+import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.option.altertable.DialectAddColumnOption;
 import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.option.altertable.DialectAlterTableOption;
 import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.option.function.DialectFunctionOption;
 import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.option.pagination.DialectPaginationOption;
@@ -76,7 +77,7 @@ public final class SQLServerDatabaseMetaData implements DialectDatabaseMetaData
     
     @Override
     public Optional<DialectAlterTableOption> getAlterTableOption() {
-        return Optional.of(new DialectAlterTableOption(true, false));
+        return Optional.of(new DialectAlterTableOption(false, false, false, new DialectAddColumnOption("ADD", "")));
     }
     
     @Override
diff --git a/database/connector/dialect/sqlserver/src/test/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/SQLServerDatabaseMetaDataTest.java b/database/connector/dialect/sqlserver/src/test/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/SQLServerDatabaseMetaDataTest.java
index 37e6fff3bf6..314ddcd41e4 100644
--- a/database/connector/dialect/sqlserver/src/test/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/SQLServerDatabaseMetaDataTest.java
+++ b/database/connector/dialect/sqlserver/src/test/java/org/apache/shardingsphere/database/connector/sql92/sqlserver/metadata/database/SQLServerDatabaseMetaDataTest.java
@@ -96,8 +96,9 @@ class SQLServerDatabaseMetaDataTest {
     void assertGetAlterTableOption() {
         Optional<DialectAlterTableOption> actual = dialectDatabaseMetaData.getAlterTableOption();
         assertTrue(actual.isPresent());
-        assertTrue(actual.map(DialectAlterTableOption::isSupportMergeDropColumns).orElse(false));
+        assertFalse(actual.map(DialectAlterTableOption::isSupportMergeDropColumns).orElse(true));
         assertFalse(actual.map(DialectAlterTableOption::isContainsParenthesesOnMergeDropColumns).orElse(true));
+        assertFalse(actual.map(DialectAlterTableOption::isContainsParenthesesOnColumnsClause).orElse(true));
     }
     
     @Test
diff --git a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/ddl/EncryptAlterTableTokenGenerator.java b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/ddl/EncryptAlterTableTokenGenerator.java
index 764e17868d5..810819e623b 100644
--- a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/ddl/EncryptAlterTableTokenGenerator.java
+++ b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/ddl/EncryptAlterTableTokenGenerator.java
@@ -19,7 +19,9 @@ package org.apache.shardingsphere.encrypt.rewrite.token.generator.ddl;
 
 import lombok.RequiredArgsConstructor;
 import lombok.Setter;
+import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.option.altertable.DialectAddColumnOption;
 import org.apache.shardingsphere.database.connector.core.metadata.database.metadata.option.altertable.DialectAlterTableOption;
+import org.apache.shardingsphere.database.connector.core.type.DatabaseType;
 import org.apache.shardingsphere.database.connector.core.type.DatabaseTypeRegistry;
 import org.apache.shardingsphere.encrypt.constant.EncryptColumnDataType;
 import org.apache.shardingsphere.encrypt.exception.metadata.EncryptColumnAlterException;
@@ -28,6 +30,7 @@ import org.apache.shardingsphere.encrypt.rewrite.token.pojo.EncryptColumnToken;
 import org.apache.shardingsphere.encrypt.rule.EncryptRule;
 import org.apache.shardingsphere.encrypt.rule.column.EncryptColumn;
 import org.apache.shardingsphere.encrypt.rule.column.item.AssistedQueryColumnItem;
+import org.apache.shardingsphere.encrypt.rule.column.item.CipherColumnItem;
 import org.apache.shardingsphere.encrypt.rule.column.item.LikeQueryColumnItem;
 import org.apache.shardingsphere.encrypt.rule.table.EncryptTable;
 import org.apache.shardingsphere.encrypt.spi.EncryptAlgorithm;
@@ -45,6 +48,7 @@ import org.apache.shardingsphere.sql.parser.statement.core.segment.ddl.column.al
 import org.apache.shardingsphere.sql.parser.statement.core.segment.ddl.column.alter.ModifyColumnDefinitionSegment;
 import org.apache.shardingsphere.sql.parser.statement.core.segment.ddl.column.position.ColumnPositionSegment;
 import org.apache.shardingsphere.sql.parser.statement.core.segment.dml.column.ColumnSegment;
+import org.apache.shardingsphere.sql.parser.statement.core.statement.SQLStatement;
 import org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.table.AlterTableStatement;
 
 import java.util.Collection;
@@ -52,6 +56,9 @@ import java.util.Collections;
 import java.util.LinkedList;
 import java.util.List;
 import java.util.Optional;
+import java.util.function.Function;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
 import java.util.stream.Collectors;
 
 /**
@@ -61,6 +68,10 @@ import java.util.stream.Collectors;
 @Setter
 public final class EncryptAlterTableTokenGenerator implements CollectionSQLTokenGenerator<CommonSQLStatementContext> {
     
+    private static final Pattern DEFAULT_LITERAL_PATTERN = Pattern.compile("(?i)\\bDEFAULT\\s+('(?:''|[^'])*'|[-+]?\\d+(?:\\.\\d+)?)");
+    
+    private static final String NOT_NULL_CLAUSE = " NOT NULL";
+    
     private final EncryptRule rule;
     
     @Override
@@ -73,7 +84,7 @@ public final class EncryptAlterTableTokenGenerator implements CollectionSQLToken
         AlterTableStatement sqlStatement = (AlterTableStatement) sqlStatementContext.getSqlStatement();
         String tableName = sqlStatement.getTable().getTableName().getIdentifier().getValue();
         EncryptTable encryptTable = rule.getEncryptTable(tableName);
-        Collection<SQLToken> result = new LinkedList<>(getAddColumnTokens(encryptTable, sqlStatement.getAddColumnDefinitions()));
+        Collection<SQLToken> result = new LinkedList<>(getAddColumnTokens(encryptTable, sqlStatement.getAddColumnDefinitions(), sqlStatementContext));
         result.addAll(getModifyColumnTokens(encryptTable, sqlStatement.getModifyColumnDefinitions()));
         result.addAll(getChangeColumnTokens(encryptTable, sqlStatement.getChangeColumnDefinitions()));
         List<SQLToken> dropColumnTokens = getDropColumnTokens(encryptTable, sqlStatement.getDropColumnDefinitions());
@@ -86,39 +97,118 @@ public final class EncryptAlterTableTokenGenerator implements CollectionSQLToken
         return result;
     }
     
-    private Collection<SQLToken> getAddColumnTokens(final EncryptTable encryptTable, final Collection<AddColumnDefinitionSegment> segments) {
+    private Collection<SQLToken> getAddColumnTokens(final EncryptTable encryptTable, final Collection<AddColumnDefinitionSegment> segments,
+                                                    final CommonSQLStatementContext sqlStatementContext) {
         Collection<SQLToken> result = new LinkedList<>();
         for (AddColumnDefinitionSegment each : segments) {
-            result.addAll(getAddColumnTokens(encryptTable, each));
+            result.addAll(getAddColumnTokens(encryptTable, each, sqlStatementContext));
         }
         return result;
     }
     
-    private Collection<SQLToken> getAddColumnTokens(final EncryptTable encryptTable, final AddColumnDefinitionSegment segment) {
+    private Collection<SQLToken> getAddColumnTokens(final EncryptTable encryptTable, final AddColumnDefinitionSegment segment, final CommonSQLStatementContext sqlStatementContext) {
         Collection<SQLToken> result = new LinkedList<>();
         for (ColumnDefinitionSegment each : segment.getColumnDefinitions()) {
             String columnName = each.getColumnName().getIdentifier().getValue();
             if (encryptTable.isEncryptColumn(columnName)) {
-                result.addAll(getAddColumnTokens(encryptTable.getEncryptColumn(columnName), segment, each));
+                result.addAll(getAddColumnTokens(encryptTable.getEncryptColumn(columnName), segment, each, sqlStatementContext.getSqlStatement(), encryptTable.getTable()));
             }
         }
         getAddColumnPositionToken(encryptTable, segment).ifPresent(result::add);
         return result;
     }
     
-    private Collection<SQLToken> getAddColumnTokens(final EncryptColumn encryptColumn,
-                                                    final AddColumnDefinitionSegment addColumnDefinitionSegment, final ColumnDefinitionSegment columnDefinitionSegment) {
+    private Collection<SQLToken> getAddColumnTokens(final EncryptColumn encryptColumn, final AddColumnDefinitionSegment addColumnDefinitionSegment,
+                                                    final ColumnDefinitionSegment columnDefinitionSegment, final SQLStatement sqlStatement, final String tableName) {
         Collection<SQLToken> result = new LinkedList<>();
         result.add(new RemoveToken(columnDefinitionSegment.getStartIndex(), columnDefinitionSegment.getStopIndex()));
         result.add(new EncryptColumnToken(columnDefinitionSegment.getStopIndex() + 1, columnDefinitionSegment.getStopIndex(),
-                encryptColumn.getCipher().getName(), EncryptColumnDataType.DEFAULT_DATA_TYPE));
+                createFirstAddColumnName(sqlStatement.getDatabaseType(), encryptColumn.getCipher().getName()),
+                appendCipherColumnDefault(EncryptColumnDataType.DEFAULT_DATA_TYPE, encryptColumn.getCipher(), tableName, columnDefinitionSegment)));
         encryptColumn.getAssistedQuery().map(optional -> new EncryptColumnToken(addColumnDefinitionSegment.getStopIndex() + 1,
-                addColumnDefinitionSegment.getStopIndex(), ", ADD COLUMN " + optional.getName(), EncryptColumnDataType.DEFAULT_DATA_TYPE)).ifPresent(result::add);
+                addColumnDefinitionSegment.getStopIndex(), createDerivedAddColumnName(sqlStatement.getDatabaseType(), optional.getName()),
+                appendAssistedQueryDefault(EncryptColumnDataType.DEFAULT_DATA_TYPE, optional, tableName, columnDefinitionSegment)))
+                .ifPresent(result::add);
         encryptColumn.getLikeQuery().map(optional -> new EncryptColumnToken(addColumnDefinitionSegment.getStopIndex() + 1,
-                addColumnDefinitionSegment.getStopIndex(), ", ADD COLUMN " + optional.getName(), EncryptColumnDataType.DEFAULT_DATA_TYPE)).ifPresent(result::add);
+                addColumnDefinitionSegment.getStopIndex(), createDerivedAddColumnName(sqlStatement.getDatabaseType(), optional.getName()),
+                appendLikeQueryDefault(EncryptColumnDataType.DEFAULT_DATA_TYPE, optional, tableName, columnDefinitionSegment)))
+                .ifPresent(result::add);
+        createAddColumnClauseCloseToken(sqlStatement.getDatabaseType(), addColumnDefinitionSegment).ifPresent(result::add);
         return result;
     }
     
+    private String createFirstAddColumnName(final DatabaseType databaseType, final String columnName) {
+        return isContainsParenthesesOnColumnsClause(databaseType) ? "(" + columnName : columnName;
+    }
+    
+    private String createDerivedAddColumnName(final DatabaseType databaseType, final String columnName) {
+        return createDerivedAddColumnOperationType(databaseType) + " " + columnName;
+    }
+    
+    private String createDerivedAddColumnOperationType(final DatabaseType databaseType) {
+        String prefix = new DatabaseTypeRegistry(databaseType).getDialectDatabaseMetaData().getAlterTableOption()
+                .flatMap(DialectAlterTableOption::getAddColumnOption).map(DialectAddColumnOption::getBeforeEachAddColumn).orElse("ADD COLUMN ").trim();
+        return prefix.isEmpty() ? "," : ", " + prefix;
+    }
+    
+    private Optional<SQLToken> createAddColumnClauseCloseToken(final DatabaseType databaseType, final AddColumnDefinitionSegment addColumnDefinitionSegment) {
+        return isContainsParenthesesOnColumnsClause(databaseType)
+                ? Optional.of(new EncryptColumnToken(addColumnDefinitionSegment.getStopIndex() + 1, addColumnDefinitionSegment.getStopIndex(), ")", null))
+                : Optional.empty();
+    }
+    
+    private boolean isContainsParenthesesOnColumnsClause(final DatabaseType databaseType) {
+        return new DatabaseTypeRegistry(databaseType).getDialectDatabaseMetaData().getAlterTableOption().map(DialectAlterTableOption::isContainsParenthesesOnColumnsClause).orElse(false);
+    }
+    
+    private String appendCipherColumnDefault(final String dataType, final CipherColumnItem cipherColumnItem, final String tableName, final ColumnDefinitionSegment columnDefinitionSegment) {
+        return appendEncryptedDefault(dataType, columnDefinitionSegment,
+                plainValue -> cipherColumnItem.encrypt("", "", tableName, columnDefinitionSegment.getColumnName().getIdentifier().getValue(), plainValue));
+    }
+    
+    private String appendAssistedQueryDefault(final String dataType, final AssistedQueryColumnItem assistedQueryColumnItem, final String tableName,
+                                              final ColumnDefinitionSegment columnDefinitionSegment) {
+        return appendEncryptedDefault(dataType, columnDefinitionSegment,
+                plainValue -> assistedQueryColumnItem.encrypt("", "", tableName, columnDefinitionSegment.getColumnName().getIdentifier().getValue(), plainValue));
+    }
+    
+    private String appendLikeQueryDefault(final String dataType, final LikeQueryColumnItem likeQueryColumnItem, final String tableName,
+                                          final ColumnDefinitionSegment columnDefinitionSegment) {
+        return appendEncryptedDefault(dataType, columnDefinitionSegment,
+                plainValue -> likeQueryColumnItem.encrypt("", "", tableName, columnDefinitionSegment.getColumnName().getIdentifier().getValue(), plainValue));
+    }
+    
+    private String appendEncryptedDefault(final String dataType, final ColumnDefinitionSegment columnDefinitionSegment, final Function<Object, Object> encryptor) {
+        return getDefaultLiteral(columnDefinitionSegment).map(optional -> appendDefault(dataType, createDefaultLiteral(encryptor.apply(createDefaultPlainValue(optional)))))
+                .map(optional -> appendNotNull(optional, columnDefinitionSegment)).orElseGet(() -> appendNotNull(dataType, columnDefinitionSegment));
+    }
+    
+    private Optional<String> getDefaultLiteral(final ColumnDefinitionSegment columnDefinitionSegment) {
+        Matcher matcher = DEFAULT_LITERAL_PATTERN.matcher(columnDefinitionSegment.getText());
+        return matcher.find() ? Optional.of(matcher.group(1)) : Optional.empty();
+    }
+    
+    private Object createDefaultPlainValue(final String defaultLiteral) {
+        return isQuotedDefaultLiteral(defaultLiteral) ? defaultLiteral.substring(1, defaultLiteral.length() - 1).replace("''", "'") : defaultLiteral;
+    }
+    
+    private boolean isQuotedDefaultLiteral(final String defaultLiteral) {
+        return defaultLiteral.length() > 1 && '\'' == defaultLiteral.charAt(0) && '\'' == defaultLiteral.charAt(defaultLiteral.length() - 1);
+    }
+    
+    private String createDefaultLiteral(final Object defaultValue) {
+        return "'" + String.valueOf(defaultValue).replace("'", "''") + "'";
+    }
+    
+    private String appendDefault(final String dataType, final String defaultLiteral) {
+        int notNullIndex = dataType.toUpperCase().lastIndexOf(NOT_NULL_CLAUSE);
+        return notNullIndex < 0 ? dataType + " DEFAULT " + defaultLiteral : dataType.substring(0, notNullIndex) + " DEFAULT " + defaultLiteral + dataType.substring(notNullIndex);
+    }
+    
+    private String appendNotNull(final String dataType, final ColumnDefinitionSegment columnDefinitionSegment) {
+        return columnDefinitionSegment.isNotNull() && !dataType.toUpperCase().contains(NOT_NULL_CLAUSE) ? dataType + NOT_NULL_CLAUSE : dataType;
+    }
+    
     private Optional<SQLToken> getAddColumnPositionToken(final EncryptTable encryptTable, final AddColumnDefinitionSegment segment) {
         Optional<ColumnPositionSegment> columnPositionSegment = segment.getColumnPosition().filter(optional -> null != optional.getColumnName());
         if (columnPositionSegment.isPresent()) {
@@ -210,14 +300,14 @@ public final class EncryptAlterTableTokenGenerator implements CollectionSQLToken
     private Collection<SQLToken> getColumnTokens(final EncryptColumn previousEncryptColumn, final EncryptColumn encryptColumn, final ChangeColumnDefinitionSegment segment) {
         Collection<SQLToken> result = new LinkedList<>();
         result.add(new RemoveToken(segment.getColumnDefinition().getColumnName().getStartIndex(), segment.getColumnDefinition().getStopIndex()));
-        result.add(new EncryptColumnToken(segment.getColumnDefinition().getStopIndex() + 1, segment.getColumnDefinition().getStopIndex(),
-                encryptColumn.getCipher().getName(), EncryptColumnDataType.DEFAULT_DATA_TYPE));
+        result.add(new EncryptColumnToken(segment.getColumnDefinition().getStopIndex() + 1, segment.getColumnDefinition().getStopIndex(), encryptColumn.getCipher().getName(),
+                EncryptColumnDataType.DEFAULT_DATA_TYPE));
         previousEncryptColumn.getAssistedQuery().map(optional -> new EncryptColumnToken(segment.getStopIndex() + 1, segment.getStopIndex(),
-                ", CHANGE COLUMN " + optional.getName() + " " + encryptColumn.getAssistedQuery().map(AssistedQueryColumnItem::getName).orElse(""),
-                EncryptColumnDataType.DEFAULT_DATA_TYPE)).ifPresent(result::add);
+                ", CHANGE COLUMN " + optional.getName() + " " + encryptColumn.getAssistedQuery().map(AssistedQueryColumnItem::getName).orElse(""), EncryptColumnDataType.DEFAULT_DATA_TYPE))
+                .ifPresent(result::add);
         previousEncryptColumn.getLikeQuery().map(optional -> new EncryptColumnToken(segment.getStopIndex() + 1, segment.getStopIndex(),
-                ", CHANGE COLUMN " + optional.getName() + " " + encryptColumn.getLikeQuery().map(LikeQueryColumnItem::getName).orElse(""),
-                EncryptColumnDataType.DEFAULT_DATA_TYPE)).ifPresent(result::add);
+                ", CHANGE COLUMN " + optional.getName() + " " + encryptColumn.getLikeQuery().map(LikeQueryColumnItem::getName).orElse(""), EncryptColumnDataType.DEFAULT_DATA_TYPE))
+                .ifPresent(result::add);
         return result;
     }
     
@@ -228,8 +318,8 @@ public final class EncryptAlterTableTokenGenerator implements CollectionSQLToken
     private Collection<SQLToken> getDropColumnTokens(final EncryptTable encryptTable, final DropColumnDefinitionSegment segment) {
         Collection<SQLToken> result = new LinkedList<>();
         for (ColumnSegment each : segment.getColumns()) {
-            ShardingSpherePreconditions.checkState(!encryptTable.isEncryptColumn(each.getQualifiedName()),
-                    () -> new UnsupportedOperationException("Unsupported operation 'drop' for the cipher column"));
+            ShardingSpherePreconditions.checkState(
+                    !encryptTable.isEncryptColumn(each.getQualifiedName()), () -> new UnsupportedOperationException("Unsupported operation 'drop' for the cipher column"));
         }
         return result;
     }
diff --git a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/ddl/EncryptCreateTableTokenGenerator.java b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/ddl/EncryptCreateTableTokenGenerator.java
index e74103380dd..e9d66e3d74b 100644
--- a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/ddl/EncryptCreateTableTokenGenerator.java
+++ b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/ddl/EncryptCreateTableTokenGenerator.java
@@ -84,12 +84,10 @@ public final class EncryptCreateTableTokenGenerator implements CollectionSQLToke
     }
     
     private Optional<? extends SQLToken> getAssistedQueryColumnToken(final EncryptColumn encryptColumn, final ColumnDefinitionSegment column) {
-        return encryptColumn.getAssistedQuery()
-                .map(optional -> new ColumnDefinitionToken(encryptColumn.getAssistedQuery().get().getName(), EncryptColumnDataType.DEFAULT_DATA_TYPE, column.getStartIndex()));
+        return encryptColumn.getAssistedQuery().map(optional -> new ColumnDefinitionToken(optional.getName(), EncryptColumnDataType.DEFAULT_DATA_TYPE, column.getStartIndex()));
     }
     
     private Optional<? extends SQLToken> getLikeQueryColumnToken(final EncryptColumn encryptColumn, final ColumnDefinitionSegment column) {
-        return encryptColumn.getLikeQuery()
-                .map(optional -> new ColumnDefinitionToken(encryptColumn.getLikeQuery().get().getName(), EncryptColumnDataType.DEFAULT_DATA_TYPE, column.getStartIndex()));
+        return encryptColumn.getLikeQuery().map(optional -> new ColumnDefinitionToken(optional.getName(), EncryptColumnDataType.DEFAULT_DATA_TYPE, column.getStartIndex()));
     }
 }
diff --git a/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/ddl/EncryptAlterTableTokenGeneratorTest.java b/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/ddl/EncryptAlterTableTokenGeneratorTest.java
index dde574f11aa..98910e66d75 100644
--- a/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/ddl/EncryptAlterTableTokenGeneratorTest.java
+++ b/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/ddl/EncryptAlterTableTokenGeneratorTest.java
@@ -47,11 +47,13 @@ import org.junit.jupiter.api.Test;
 import java.util.Collection;
 import java.util.Collections;
 import java.util.Iterator;
+import java.util.Optional;
 
-import static org.hamcrest.Matchers.is;
 import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.is;
 import static org.hamcrest.Matchers.isA;
 import static org.junit.jupiter.api.Assertions.assertThrows;
+import static org.mockito.Mockito.RETURNS_DEEP_STUBS;
 import static org.mockito.Mockito.mock;
 import static org.mockito.Mockito.when;
 
@@ -59,6 +61,8 @@ class EncryptAlterTableTokenGeneratorTest {
     
     private final DatabaseType databaseType = TypedSPILoader.getService(DatabaseType.class, "FIXTURE");
     
+    private final EncryptAlgorithm encryptAlgorithm = mock(EncryptAlgorithm.class, RETURNS_DEEP_STUBS);
+    
     private EncryptAlterTableTokenGenerator generator;
     
     @BeforeEach
@@ -80,21 +84,23 @@ class EncryptAlterTableTokenGeneratorTest {
         when(result.getEncryptColumn("certificate_number")).thenReturn(mockEncryptColumn());
         when(result.isEncryptColumn("certificate_number_new")).thenReturn(true);
         when(result.getEncryptColumn("certificate_number_new")).thenReturn(mockNewEncryptColumn());
+        when(result.findEncryptor("certificate_number")).thenReturn(Optional.of(encryptAlgorithm));
+        when(result.findEncryptor("certificate_number_new")).thenReturn(Optional.of(encryptAlgorithm));
         return result;
     }
     
     private EncryptColumn mockEncryptColumn() {
-        EncryptColumn result = new EncryptColumn("certificate_number", new CipherColumnItem("cipher_certificate_number", mock(EncryptAlgorithm.class)));
-        result.setAssistedQuery(new AssistedQueryColumnItem("assisted_certificate_number", mock(EncryptAlgorithm.class)));
-        result.setLikeQuery(new LikeQueryColumnItem("like_certificate_number", mock(EncryptAlgorithm.class)));
+        EncryptColumn result = new EncryptColumn("certificate_number", new CipherColumnItem("cipher_certificate_number", encryptAlgorithm));
+        result.setAssistedQuery(new AssistedQueryColumnItem("assisted_certificate_number", encryptAlgorithm));
+        result.setLikeQuery(new LikeQueryColumnItem("like_certificate_number", encryptAlgorithm));
         return result;
     }
     
     private EncryptColumn mockNewEncryptColumn() {
         EncryptColumn result = new EncryptColumn(
-                "certificate_number_new", new CipherColumnItem("cipher_certificate_number_new", mock(EncryptAlgorithm.class)));
-        result.setAssistedQuery(new AssistedQueryColumnItem("assisted_certificate_number_new", mock(EncryptAlgorithm.class)));
-        result.setLikeQuery(new LikeQueryColumnItem("like_certificate_number_new", mock(EncryptAlgorithm.class)));
+                "certificate_number_new", new CipherColumnItem("cipher_certificate_number_new", encryptAlgorithm));
+        result.setAssistedQuery(new AssistedQueryColumnItem("assisted_certificate_number_new", encryptAlgorithm));
+        result.setLikeQuery(new LikeQueryColumnItem("like_certificate_number_new", encryptAlgorithm));
         return result;
     }
     
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/ddl/alter/alter-table.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/ddl/alter/alter-table.xml
index 82ed77bb961..e55025b301d 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/ddl/alter/alter-table.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/ddl/alter/alter-table.xml
@@ -34,7 +34,7 @@
     
     <rewrite-assertion id="alter_table_add_column_for_cipher" db-types="MySQL">
         <input sql="/* SHARDINGSPHERE_HINT: SKIP_METADATA_VALIDATE=true */ ALTER TABLE t_account_bak ADD COLUMN id int not null, ADD COLUMN password varchar(255) not null default ''" />
-        <output sql="ALTER TABLE t_account_bak ADD COLUMN id int not null, ADD COLUMN cipher_password VARCHAR(4000), ADD COLUMN assisted_query_password VARCHAR(4000), ADD COLUMN like_query_password VARCHAR(4000)" />
+        <output sql="ALTER TABLE t_account_bak ADD COLUMN id int not null, ADD COLUMN cipher_password VARCHAR(4000) DEFAULT 'encrypt_' NOT NULL, ADD COLUMN assisted_query_password VARCHAR(4000) DEFAULT 'assisted_query_' NOT NULL, ADD COLUMN like_query_password VARCHAR(4000) DEFAULT 'like_query_' NOT NULL" />
     </rewrite-assertion>
     
     <rewrite-assertion id="alter_table_add_index_for_cipher" db-types="MySQL">