[mySQL]关于add table的一个注意小事项，直接影响后续populate table

最新推荐文章于 2025-02-15 22:41:03 发布

原创最新推荐文章于 2025-02-15 22:41:03 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mySQL

web Develop 专栏收录该内容

7 篇文章

订阅专栏

本文介绍如何使用PHP连接数据库并创建带有自增主键的表。通过实例展示了自增主键的作用及重要性，即允许插入任意数量记录而不会因主键重复导致错误。

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Add a table to database</title>
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <style>
        h1 {
            color: purple;
        }
        
        h3 {
            color: #42d5ce;
        }
        
        .containingDiv {
            border: 1px solid #7c73f6;
            margin-top: 100px;
            border-radius: 15px;
        }
    </style>

</head>

<body>
    <div class="container-fluid">
        <div class="row">
            <div class="col-sm-offset-1 col-sm-10 containingDiv">
                <h1>Add a table to database:</h1>
                <h3>Connect to database</h3>
                <?php
                    //mysqli_connect(host, username, password, dbname)
                    $link = @mysqli_connect("localhost", "cdwccdwc_user", "bobo1989..", "cdwccdwc_users") or die("ERROR: Unable to connect: " . mysqli_connect_error());

                    echo "<p>Connected successfully to the database.</p>";
                    ?>
                
                <h3>Create table</h3>
                <?php
                    $sql = "CREATE TABLE users3(ID INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT, firstname CHAR(20) NOT NULL, lastname CHAR(20) NOT NULL, email VARCHAR(30), password VARCHAR(40))";//AUTO_INCREMENT is very important,because if not add it, you can just insert all the values only once.
                    if(mysqli_query($link, $sql)){
                        echo "<p>Table users created successfuly!</p>";   
                    }else{
                        echo "ERROR: Unable to execute $sql" . mysqli_error($link);   
                    }
                ?>

            </div>
        </div>
    </div>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
    <script src="js/bootstrap.min.js"></script>
</body>

</html>

如图，红色加粗标记的AUTO_INCREMENT,在创建table的时候加与不加的区别在于：

加AUTO_INCREMENT：后续populate table 的时候可以添加任意数量的表行内容，因为entry for key "primary"在自动增加

不加AUTO_INCREMENT：上面操作只能添加一次，会报错，例如：ERROR: Unable to excecute: INSERT INTO users3 (firstname, lastname, email, password) VALUES ('tom', 'cluz', 'tm@gmial.com', 'ef0c0fd6710ab42cd8595bee67c65424'). Duplicate entry '0' for key 'PRIMARY'。