CREATE TABLE IF NOT EXISTS 失败，表已经存在

2021-12-25 00:00:00 sql php mysql mysqli

我有以下代码:

$db_host = 'localhost';
$db_port = '3306';
$db_username = 'root';
$db_password = 'root';
$db_primaryDatabase = 'dsl_ams';

// Connect to the database, using the predefined database variables in /assets/repository/mysql.php
$dbConnection = new mysqli($db_host, $db_username, $db_password, $db_primaryDatabase);

// If there are errors (if the no# of errors is > 1), print out the error and cancel loading the page via exit();
if (mysqli_connect_errno()) {
    printf("Could not connect to MySQL databse: %s
", mysqli_connect_error());
    exit();
}

$queryCreateUsersTable = "CREATE TABLE IF NOT EXISTS `USERS` (
    `ID` int(11) unsigned NOT NULL auto_increment,
    `EMAIL` varchar(255) NOT NULL default '',
    `PASSWORD` varchar(255) NOT NULL default '',
    `PERMISSION_LEVEL` tinyint(1) unsigned NOT NULL default '1',
    `APPLICATION_COMPLETED` boolean NOT NULL default '0',
    `APPLICATION_IN_PROGRESS` boolean NOT NULL default '0',
    PRIMARY KEY  (`ID`)
)";

if(!$dbConnection->query($queryCreateUsersTable)){
    echo "Table creation failed: (" . $dbConnection->errno . ") " . $dbConnection->error;
}

哪些输出...

Table creation failed: (1050) Table '`dsl_ams`.`USERS`' already exists

我不明白的是:如果该表已经存在，IF NOT EXISTS 是否应该取消 SQL 查询的执行?换句话说，如果该表存在，它是否应该退出该 if 语句并且根本不回显任何内容，并且不尝试执行查询?

What I don't understand is: isn't IF NOT EXISTS supposed to cancel the execution of the SQL query if that table already exists? In other words, if the table exists, shouldn't it exit that if statement and not echo anything out at all, and not attempt to execute the query?

只是试图找到如果表不存在则创建一个表"的最佳方法，而不向用户输出任何内容.

Just trying to find the best way to "create a table if it doesn't exist" without outputting anything to the user.

推荐答案

试试这个

$query = "SELECT ID FROM USERS";
$result = mysqli_query($dbConnection, $query);

if(empty($result)) {
                $query = "CREATE TABLE USERS (
                          ID int(11) AUTO_INCREMENT,
                          EMAIL varchar(255) NOT NULL,
                          PASSWORD varchar(255) NOT NULL,
                          PERMISSION_LEVEL int,
                          APPLICATION_COMPLETED int,
                          APPLICATION_IN_PROGRESS int,
                          PRIMARY KEY  (ID)
                          )";
                $result = mysqli_query($dbConnection, $query);
}

这会检查表格中是否有任何内容，如果返回NULL，则说明您没有表格.

This checks to see if anything is in the table and if it returns NULL you don't have a table.

此外，mysql 中没有 BOOLEAN 数据类型，您应该 INT 并在插入表时将其设置为 1 或 0.您也不需要将所有内容都用单引号括起来，只是在将数据硬编码到查询中时即可.

Also there is no BOOLEAN datatype in mysql, you should INT and just set it to 1 or 0 when inserting into the table. You also don't need single quotes around everything, just when you are hardcoding data into the query.

像这样...

$query = "INSERT INTO USERS (EMAIL, PASSWORD, PERMISSION_LEVEL, APPLICATION_COMPLETED, APPLICATION_IN_PROGRESS) VALUES ('foobar@foobar.com', 'fjsdfbsjkbgs', 0, 0, 0)";

相关文章