如果不存在则创建表失败，并且表已存在 [英] CREATE TABLE IF NOT EXISTS fails with table already exists

问题描述

我有以下代码:

$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\n", 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

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 ('[e-mail]', 'fjsdfbsjkbgs', 0, 0, 0)";

希望这会有所帮助.

这篇关于如果不存在则创建表失败，并且表已存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！