php - 当 sql 列类型从 TEXT 更改为 varchar 时无法登录到 php 页面

Question

当密码列类型为 TEXT 时，我的登录页面过去可以正常工作。我已经更新了表内容并用于对密码执行 sql 密码函数，但现在格式为 varchar，页面将无法进行身份验证。

下面是有问题的代码:

<main>
            <?php 
                if(isset($_POST['submit'])){
                    include 'databaselogin.php';

                    $username = ucfirst(strtolower($_POST['username']));
                    $username = mysqli_real_escape_string($mysqli, $username);
                    $password = $_POST['password'];

                    $sql = "SELECT *
                            FROM teams
                            WHERE name = '$username'";
                    $result = $mysqli->query($sql) or die($mysqli->error.__LINE__);

                    if($result->num_rows == 1){
                        $row = $result->fetch_assoc();
                        $hash = $row["password"];

                        if(password_verify($password,$hash)){
                            session_start();
                            $_SESSION["username"] = $row["name"];

                            header("Location: http://www.josephade.com/fyp/dashboard.php");
                            die();
                        }
                    }else{
                        //Show error incorrect password
                        include 'error.php';
                    }

                    mysqli_close($mysqli);
                }
            ?>

Answer 1

在 MySQL 文档中找到的 BLOB 和 TEXT 都是相关的。

• http://dev.mysql.com/doc/refman/5.6/en/blob.html

11.4.3 BLOB 和 TEXT 类型

If strict SQL mode is not enabled and you assign a value to a BLOB or TEXT column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode.

这可以解释数据丢失。

• 因此，如我的评论中所述，需要从 TEXT 到 VARCHAR 进行全面重建:

"you may have to rebuild your hashes then"

---

我找到的另一个链接:

• http://nicj.net/mysql-text-vs-varchar-performance/

从该页面的部分检索:

Starting with MySQL 5.0.3, the maximum field length for VARCHAR fields was increased from 255 characters to 65,535 characters. This is good news, as VARCHAR fields, as opposed to TEXT fields, are stored in-row for the MyISAM storage engine (InnoDB has different characteristics). TEXT and BLOB fields are not stored in-row — they require a separate lookup (and a potential disk read) if their column is included in a SELECT clause. Additionally, the inclusion of a TEXT or BLOB column in any sort will force the sort to use a disk-based temporary table, as the MEMORY (HEAP) storage engine, which is used for temporary tables, requires.

Thus, the benefits of using a VARCHAR field instead of TEXT for columns between 255 and 65k characters seem obvious at first glance in some scenarios: potentially less disk reads (for queries including the column, as there is no out-of-row data) and less writes (for queries with sorts including the column).