MySQL - 修改列类型

Table of content
上一页
下一页

在 MySQL 中，用户可以灵活地修改字段的数据类型。这在您最初为新数据库表中的列设置了错误的数据类型时或需要调整特定列中值的存储大小时非常有用。

您可以使用 ALTER TABLE 命令修改或更新 MySQL 表中的列类型。

MySQL ALTER TABLE 命令

ALTER TABLE 命令用于修改与数据库表相关的任何数据。这些数据可以是表中的记录，也可以是表定义本身。更改列的数据类型是可以使用 ALTER TABLE 命令执行的一种此类修改。

有两种方法可以更改列的数据类型：ALTER TABLE... MODIFY 命令和 ALTER TABLE... CHANGE 命令。

MySQL ALTER TABLE... MODIFY 命令

您可以将 MODIFY 方法与 ALTER TABLE 语句一起使用以更改列/字段数据类型。

语法

以下是使用 ALTER TABLE... MODIFY 命令更改列类型的基本语法：

ALTER TABLE table_name MODIFY column_name new_datatype;

示例

假设您使用以下查询创建了一个名为“test_table”的表：

CREATE TABLE test_table (
   field1 INT,
   field2 VARCHAR(100),
   field3 DATE,
   PRIMARY KEY(field1)
);

要检查表是否已成功创建，请使用如下所示的 DESC 命令：

DESC test_table;

这将显示表结构如下：

字段 类型 空 键 默认值 额外
field1 int 否 PRI NULL
field2 varchar(100) 是 NULL
field3 date 是 NULL

现在，假设您想将“field2”的数据类型从 VARCHAR(100) 更改为 TEXT 以获得更高的简便性。您可以使用 ALTER TABLE... MODIFY 查询如下所示：

ALTER TABLE test_table MODIFY field2 TEXT;

以下是获得的输出：

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

要检查列数据类型是否已成功更改，请使用如下所示的 DESC 命令：

DESC test_table;

这将显示修改后的表结构如下：

字段 类型 空 键 默认值 额外
field1 int 否 PRI NULL
field2 text 是 NULL
field3 date 是 NULL

MySQL ALTER TABLE... CHANGE 命令

您还可以将 CHANGE 方法与 ALTER TABLE 语句一起使用以修改列的数据类型。此方法用于在表创建后更改与表相关的所有数据。

CHANGE 和 MODIFY 方法之间的唯一区别在于 MODIFY 方法不能重命名列，而 CHANGE 方法可以。

语法

以下是使用 ALTER TABLE... CHANGE 命令更改列类型的基本语法：

ALTER TABLE table_name CHANGE column_name column_name new_datatype;

每当使用 CHANGE 方法时，您必须记住在查询中两次指定列名。

示例

在此示例中，我们使用 ALTER TABLE 命令中的 CHANGE 方法更改“test_table”中“field3”的数据类型。首先，让我们使用 DESC 命令观察表的当前定义：

DESC test_table;

以下是生成的表：

字段 类型 空 键 默认值 额外
field1 int 否 PRI NULL
field2 text 是 NULL
field3 date 是 NULL

现在，使用 ALTER TABLE... CHANGE 方法，更改“field3”的列类型：

ALTER TABLE test_table CHANGE field3 field3 VARCHAR(20);

以上代码的输出如下：

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

您可以使用如下所示的 DESC 命令验证列数据类型是否已更改：

DESC test_table;

我们得到以下表作为输出：

字段 类型 空 键 默认值 额外
field1 int 否 PRI NULL
field2 text 是 NULL
field3 varchar(20) 是 NULL

使用客户端程序更改列类型

我们也可以使用客户端程序更改列类型。

语法

PHP NodeJS Java Python

要通过 PHP 程序更改列类型，我们需要使用mysqli函数query()执行“ALTER TABLE”语句，如下所示：

$sql = "ALTER TABLE test_table MODIFY field2 TEXT";
$mysqli->query($sql);

要通过 JavaScript 程序更改列类型，我们需要使用mysql2库的query()函数执行“ALTER TABLE”语句，如下所示：

sql = "ALTER TABLE test_table MODIFY field2 TEXT";
con.query(sql)

要通过 Java 程序更改列类型，我们需要使用JDBC函数executeUpdate()执行“ALTER TABLE”语句，如下所示：

String sql = "ALTER TABLE test_table MODIFY field2 TEXT";
statement.executeUpdate(sql);

要通过 Python 程序更改列类型，我们需要使用MySQL Connector/Python的execute()函数执行“ALTER TABLE”语句，如下所示：

change_column_type_query = "ALTER TABLE test_table MODIFY field2 TEXT"
cursorObj.execute(change_column_type_query)

示例

以下是程序：

PHP NodeJS Java Python
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
// Create table Myplayer    
$sql = 'CREATE TABLE IF NOT EXISTS test_table (field1 INT, field2 VARCHAR(100), field3 DATE, PRIMARY KEY(field1) )';
$result = $mysqli->query($sql);
if ($result) {
    echo "Table created successfully...!\n";
}
echo "Describe table before column modification...!\n";
$q = "DESC test_table";
if ($res = $mysqli->query($q)) {
    while ($row = $res->fetch_array()) {
        print_r($row[1]);
        printf("\n");
    }
}
//modifie table column...!
$sql = "ALTER TABLE test_table MODIFY field2 TEXT";
if ($mysqli->query($sql)) {
    echo "Table's column type modification done...!\n";
}
echo "Describe table after modification of field2...!\n";
$sql = "DESC test_table";
if ($res = $mysqli->query($sql)) {
    while ($row = $res->fetch_array()) {
        print_r($row[1]);
        printf("\n");
    }
}
$mysqli->close();

输出

获得的输出如下所示：

Table created successfully...!
Describe table before column modification...!
int
varchar(100)
date
Table's column type modification done...!
Describe table after modification of field2...!
int
text
date        


var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    sql = "CREATE TABLE test_table (field1 INT,field2 VARCHAR(100),field3 DATE,PRIMARY KEY(field1));"
    con.query(sql);

    sql = "DESC test_table;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**Description of test_table:**");
      console.log(result);
    });

    sql = "ALTER TABLE test_table MODIFY field2 TEXT;"
    con.query(sql);

    sql = "DESC test_table;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**Description of test_table after modifying:**");
      console.log(result);
    });
});

输出

获得的输出如下所示：

 
Connected!
--------------------------
**Description of test_table:**
[
  {
    Field: 'field1',
    Type: 'int',
    Null: 'NO',
    Key: 'PRI',
    Default: null,
    Extra: ''
  },
  {
    Field: 'field2',
    Type: 'varchar(100)',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'field3',
    Type: 'date',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  }
]
**Description of test_table after modifying:**
[
  {
    Field: 'field1',
    Type: 'int',
    Null: 'NO',
    Key: 'PRI',
    Default: null,
    Extra: ''
  },
  {
    Field: 'field2',
    Type: 'text',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'field3',
    Type: 'date',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  }
]    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ChangeColumnType {
   public static void main(String[] args) {
      String url = "jdbc:mysql://#:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //create a table...
         statement.execute("CREATE TABLE IF NOT EXISTS test_table (field1 INT, field2 VARCHAR(100), field3 DATE, PRIMARY KEY(field1))");
         System.out.println("Table created successfully...!");

         System.out.println("Describe table before column modification...!");
         ResultSet resultSet = statement.executeQuery("DESC test_table");
         while (resultSet.next()){
            System.out.println(resultSet.getNString(1)+" "+resultSet.getNString(2)+" "+resultSet.getNString(3)+" "+resultSet.getNString(4));
         }

         //Updating the table's column...!
         statement.executeUpdate("ALTER TABLE test_table MODIFY field2 TEXT");
         System.out.println("Table column updated successfully...!");

         System.out.println("Describe table After column modification...!");
         ResultSet resultSet1 = statement.executeQuery("DESC test_table");
         while (resultSet1.next()){
            System.out.println(resultSet1.getNString(1)+" "+resultSet1.getNString(2)+" "+resultSet1.getNString(3)+" "+resultSet1.getNString(4));
         }
         connection.close();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

输出

获得的输出如下所示：

Connected successfully...!
Table created successfully...!
Describe table before column modification...!
field1 int NO PRI
field2 text YES 
field3 date YES 
Table column updated successfully...!
Describe table After column modification...!
field1 int NO PRI
field2 text YES 
field3 date YES       

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# Creating the table 'test_table'
create_table_query = '''
CREATE TABLE test_table (
    field1 INT,
    field2 VARCHAR(100),
    field3 DATE,
    PRIMARY KEY(field1)
)'''
cursorObj.execute(create_table_query)
print("Table 'test_table' is created successfully!")
# Checking whether the table is created
desc_query = "DESC test_table;"
cursorObj.execute(desc_query)
results = cursorObj.fetchall()
print("\nTable structure for 'test_table':")
for result in results:
    print(result)
# Changing column type
change_column_type_query = "ALTER TABLE test_table MODIFY field2 TEXT"
cursorObj.execute(change_column_type_query)
print("\nColumn 'field2' type has been changed to TEXT.")
# Verification
desc = "DESC test_table;"
cursorObj.execute(desc)
results = cursorObj.fetchall()
print("\nTable structure for 'test_table':")
for result in results:
    print(result)
# Closing the cursor and connectimon
cursorObj.close()
connection.close()

输出

获得的输出如下所示：

Table 'test_table' is created successfully!
Table structure for 'test_table':
('field1', b'int', 'NO', 'PRI', None, '')
('field2', b'varchar(100)', 'YES', '', None, '')
('field3', b'date', 'YES', '', None, '')

Column 'field2' type has been changed to TEXT.

Table structure for 'test_table':
('field1', b'int', 'NO', 'PRI', None, '')
('field2', b'text', 'YES', '', None, '')
('field3', b'date', 'YES', '', None, '')
打印页面
上一页
下一页
广告