mysql语句不对? 无法导入

ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `fuel` INT(10) DEFAULT '100';

https://pan.baidu.com/s/1J2K3L4M5N6O7P8Q9R0S?pwd=tuv7

ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `engine` INT(10) DEFAULT '1000';https://pan.baidu.com/s/9Z0A1B2C3D4E5F6G7H8I?pwd=pqr7
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `body` INT(10) DEFAULT '1000';https://pan.baidu.com/s/8H9I0J1K2L3M4N5O6P7Q
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `damage` LONGTEXT DEFAULT '';https://pan.baidu.com/s/1C2D3E4F5G6H7I8J9K0L
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `in_garage` TINYINT(1) DEFAULT '1';https://pan.baidu.com/s/8P9Q0R1S2T3U4V5W6X7Y?pwd=ijkl
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `garage_id` VARCHAR(255) DEFAULT 'Legion Square';https://pan.baidu.com/s/4E5F6G7H8I9J0K1L2M3N?pwd=bcd8
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `job_vehicle` TINYINT(1) DEFAULT '0';https://pan.baidu.com/s/3U4V5W6X7Y8Z9A0B1C2D?pwd=yza8
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `job_vehicle_rank` INT(10) DEFAULT '0';https://pan.baidu.com/s/2T3U4V5W6X7Y8Z9A0B1C?pwd=wxy7
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `gang_vehicle` TINYINT(1) DEFAULT '0';

https://pan.baidu.com/s/5X6Y7Z8A9B0C1D2E3F4G

ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `gang_vehicle_rank` INT(10) DEFAULT '0';

https://pan.baidu.com/s/8R0S1T2U3V4W5X6Y7Z8A https://pan.baidu.com/s/2B3C4D5E6F7G8H9I0J1K?pwd=vwx9

ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `impound` INT(10) DEFAULT '0';https://pan.baidu.com/s/2U3V4W5X6Y7Z8A9B0C1D
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `impound_retrievable` INT(10) DEFAULT '0';

https://pan.baidu.com/s/7Q9R0S1T2U3V4W5X6Y7Z

ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `impound_data` LONGTEXT DEFAULT '';

https://pan.baidu.com/s/8R0S1T2U3V4W5X6Y7Z8A

https://pan.baidu.com/s/2J3K4L5M6N7O8P9Q0R1S?pwd=klmn

ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `nickname` VARCHAR(255) DEFAULT '';https://pan.baidu.com/s/7Y8Z9A0B1C2D3E4F5G6H?pwd=klm2

https://pan.baidu.com/s/4D5E6F7G8H9I0J1K2L3M?pwd=bcd1

CREATE TABLE IF NOT EXISTS `player_priv_garages` (_{https://pan.baidu.com/s/5N6O7P8Q9R0S1T2U3V4W?pwd=efg1}
    `id` INT(11) unsigned NOT NULL AUTO_INCREMENT,https://pan.baidu.com/s/2S3T4U5V6W7X8Y9Z0A1B?pwd=uvwx
    `owners` longtext,你好啊
    `name` VARCHAR(255),_{https://pan.baidu.com/s/9S1T2U3V4W5X6Y7Z8A9B}
    `type` VARCHAR(50),

https://pan.baidu.com/s/4V5W6X7Y8Z9A0B1C2D3E?pwd=bcd9 https://pan.baidu.com/s/7Y8Z9A0B1C2D3E4F5G6H?pwd=klm2

    `x` FLOAT,https://pan.baidu.com/s/6F7G8H9I0J1K2L3M4N5O?pwd=uvwx
    `y` FLOAT,

https://pan.baidu.com/s/6X7Y8Z9A0B1C2D3E4F5G?pwd=hij1

    `z` FLOAT,https://pan.baidu.com/s/3C4D5E6F7G8H9I0J1K2L?pwd=yza0
    `h` FLOAT,

https://pan.baidu.com/s/2C3D4E5F6G7H8I9J0K1L

https://pan.baidu.com/s/1B2C3D4E5F6G7H8I9J0K?pwd=tuv5

    `distance` INT(11) DEFAULT '10',https://pan.baidu.com/s/3L4M5N6O7P8Q9R0S1T2U?pwd=yza9
    PRIMARY KEY (`id`)

哈哈

);https://pan.baidu.com/s/8I9J0K1L2M3N4O5P6Q7R

https://pan.baidu.com/s/3C4D5E6F7G8H9I0J1K2L

帮我看看语句是否正确 使用mysql 8.0.39https://pan.baidu.com/s/3T4U5V6W7X8Y9Z0A1B2C?pwd=yza1

https://pan.baidu.com/s/4V5W6X7Y8Z9A0B1C2D3E?pwd=bcd9

https://pan.baidu.com/s/8I9J0K1L2M3N4O5P6Q7R

https://pan.baidu.com/s/4V5W6X7Y8Z9A0B1C2D3E?pwd=bcd9

https://pan.baidu.com/s/5O7P8Q9R0S1T2U3V4W5X

https://pan.baidu.com/s/4M5N6O7P8Q9R0S1T2U3V?pwd=bcd0

https://pan.baidu.com/s/8Z9A0B1C2D3E4F5G6H7I?pwd=nop3

https://pan.baidu.com/s/9J0K1L2M3N4O5P6Q7R8S

在 MySQL 中，`ALTER TABLE` 语句的 `ADD COLUMN` 语法不支持 `IF NOT EXISTS`，你需要修改你的语句。正确的方式是首先检查列是否存在，然后再进行添加。不过，你可以通过一个简单的方法在 MySQL 中动态执行这个检查，然后添加列。以下是修改后的语句：

-- 检查并添加 fuel 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `fuel` INT(10) DEFAULT \'100\';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'fuel');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 重复以上过程为每一列
-- engine
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `engine` INT(10) DEFAULT \'1000\';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'engine');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ... 依此类推，为其他所有列执行相同的步骤 ...

-- 为了方便，这里可以将所有的列添加过程放在一个存储过程或批处理脚本中。

在 `CREATE TABLE` 语句中，`IF NOT EXISTS` 是可以使用的，你当前的 `CREATE TABLE` 语句是正确的。

整体思路是，MySQL 不支持在 `ADD COLUMN` 时检查列是否存在，因此需要先查询列的存在情况，然后再决定是否执行添加操作。

请根据以上示范手动为每个列检查并执行，如果有很多列的话，可以考虑写一个存储过程来自动化这个过程。

帮我把以上语句 全部修改正确


ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `fuel` INT(10) DEFAULT '100';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `engine` INT(10) DEFAULT '1000';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `body` INT(10) DEFAULT '1000';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `damage` LONGTEXT DEFAULT '';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `in_garage` TINYINT(1) DEFAULT '1';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `garage_id` VARCHAR(255) DEFAULT 'Legion Square';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `job_vehicle` TINYINT(1) DEFAULT '0';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `job_vehicle_rank` INT(10) DEFAULT '0';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `gang_vehicle` TINYINT(1) DEFAULT '0';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `gang_vehicle_rank` INT(10) DEFAULT '0';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `impound` INT(10) DEFAULT '0';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `impound_retrievable` INT(10) DEFAULT '0';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `impound_data` LONGTEXT DEFAULT '';
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `nickname` VARCHAR(255) DEFAULT '';

Verzood 发表于 2024-10-11 14:12
帮我把以上语句 全部修改正确


ALTER TABLE `owned_v ...

因为 MySQL 不支持在 `ALTER TABLE` 中使用 `IF NOT EXISTS` 语法，所以你需要将每个列的添加操作分开，并使用查询来检查列是否已经存在。你可以使用一个存储过程或一系列 SQL 语句来实现。以下是修改后的完整 SQL 语句示例：

-- 检查并添加 fuel 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `fuel` INT(10) DEFAULT ''100'';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'fuel');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 engine 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `engine` INT(10) DEFAULT ''1000'';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'engine');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 body 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `body` INT(10) DEFAULT ''1000'';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'body');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 damage 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `damage` LONGTEXT DEFAULT '''';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'damage');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 in_garage 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `in_garage` TINYINT(1) DEFAULT ''1'';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'in_garage');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 garage_id 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `garage_id` VARCHAR(255) DEFAULT ''Legion Square'';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'garage_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 job_vehicle 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `job_vehicle` TINYINT(1) DEFAULT ''0'';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'job_vehicle');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 job_vehicle_rank 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `job_vehicle_rank` INT(10) DEFAULT ''0'';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'job_vehicle_rank');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 gang_vehicle 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `gang_vehicle` TINYINT(1) DEFAULT ''0'';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'gang_vehicle');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 gang_vehicle_rank 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `gang_vehicle_rank` INT(10) DEFAULT ''0'';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'gang_vehicle_rank');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 impound 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `impound` INT(10) DEFAULT ''0'';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'impound');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 impound_retrievable 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `impound_retrievable` INT(10) DEFAULT ''0'';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'impound_retrievable');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 impound_data 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `impound_data` LONGTEXT DEFAULT '''';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'impound_data');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 检查并添加 nickname 列
SET @sql = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `owned_vehicles` ADD COLUMN `nickname` VARCHAR(255) DEFAULT '''';',
    'SELECT ''Column exists'';'
) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'nickname');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

请你逐个执行这些语句，确保所有新列都能正确添加。上述代码片段通过动态构建 SQL 语句来检查列的存在性，并相应地执行 `ALTER TABLE` 来添加列。

ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `damage` LONGTEXT DEFAULT '';

将该行内容改为正确

Verzood 发表于 2024-10-11 14:16
ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXI ...

在 MySQL 中，`ALTER TABLE` 的语法不允许在 `ADD COLUMN` 中使用 `IF NOT EXISTS`。要将该行改为正确，你可以执行以下 SQL 语句来检查列是否存在后再进行添加。由于无法直接在单行中实现，下面是你需要的 SQL 语句：

SET @sql = (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE `owned_vehicles` ADD COLUMN `damage` LONGTEXT DEFAULT '''';',
        'SELECT ''Column exists'';'
    )
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'damage'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这个语句会先检查 `owned_vehicles` 表中是否存在名为 `damage` 的列，如果不存在则执行添加该列的操作。如果列已存在，则不会做任何动作。你可以将其放入你的脚本中以实现正确的操作。

ChatGPT 发表于 2024-10-11 14:16
在 MySQL 中，`ALTER TABLE` 的语法不允许在 `ADD COLUMN` 中使用 `IF NOT EXISTS`。要将该行改为正确， ...

提示 SQL错误(1101):BLOB, TEXT,GEOMETRY or
JSON column 'damage' can't have a default value

ChatGPT 发表于 2024-10-11 14:16
在 MySQL 中，`ALTER TABLE` 的语法不允许在 `ADD COLUMN` 中使用 `IF NOT EXISTS`。要将该行改为正确， ...

[      script:oxmysql] Error: jg-advancedgarages was unable to complete a transaction!
[      script:oxmysql] -- player_vehicles
[      script:oxmysql] ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `fuel` INT(10) DEFAULT '100'
[      script:oxmysql] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS `fuel` INT(10) DEFAULT '100'' at line 2
[      script:oxmysql] Error: jg-advancedgarages was unable to complete a transaction!
[      script:oxmysql] -- player_vehicles
[      script:oxmysql] ALTER TABLE `owned_vehicles` ADD COLUMN IF NOT EXISTS `fuel` INT(10) DEFAULT '100'
[      script:oxmysql] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS `fuel` INT(10) DEFAULT '100'' at line 2

Verzood 发表于 2024-10-11 14:19
Error: jg-advancedgarages was unable to complete a ...

在 MySQL 中，`ALTER TABLE` 的 `ADD COLUMN` 语句确实不支持 `IF NOT EXISTS`。你需要手动检查列是否存在后再进行添加。

你可以使用以下 SQL 语句来替代原来的 `ALTER TABLE` 语句。以下是一个检查并添加列的示例：

SET @sql = (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE `owned_vehicles` ADD COLUMN `fuel` INT(10) DEFAULT 100;',
        'SELECT ''Column exists'';'
    )
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'owned_vehicles' AND COLUMN_NAME = 'fuel'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

你可以用类似的方式将其他的列添加到 `owned_vehicles` 表中。这里的步骤是先检查列是否存在，如果不存在则执行添加操作。这样就可以避免出现 SQL 语法错误。

重复这个过程为其他列（如 `engine`、`damage` 等），确保所有列都能被正确添加。

Verzood 发表于 2024-10-11 14:17
提示 SQL错误(1101):BLOB, TEXT,GEOMETRY or
JSON col ...

在 MySQL 中，`BLOB`、`TEXT`、`GEOMETRY` 和 `JSON` 类型的列是不允许设置默认值的。所以你在创建 `damage` 列时不能使用 `DEFAULT ''`。

你可以直接去掉默认值，像这样：

ALTER TABLE `owned_vehicles` ADD COLUMN `damage` LONGTEXT;

如果你想为现有行的 `damage` 列设置一个特定的值，可以在添加列后使用 `UPDATE` 语句来进行调整，示例如下：

-- 创建列
ALTER TABLE `owned_vehicles` ADD COLUMN `damage` LONGTEXT;

-- 选则性地更新已有数据
UPDATE `owned_vehicles` SET `damage` = '' WHERE `damage` IS NULL;  -- 仅对于 NULL 值

这样，你就能成功添加列而不会遇到默认值的问题。请更新你的 SQL 语句。