英文:
Error 1292: Incorrect datetime value: '0000-00-00' for column 'password_changed_at' at row 1
问题
我有一个用于 Rest API 服务器的 Gin 和 upper/db MySQL ORM。我创建了一个简单的脚本，在“第一次部署”或类似情况下创建数据库和表。但是我一直在遇到 SQL 错误。
这是我的 User 模型：
type User struct {ID int64 `db:"id,omitempty" json:"id,omitempty"`Email string `db:"email" json:"email"`Password string `db:"password" json:"password,omitempty"`ProfileImage string `db:"profile_image" json:"profile_image"`PasswordChangedAt time.Time `db:"password_changed_at" json:"password_changed_at,omitempty"`CreatedAt time.Time `db:"created_at" json:"created_at"`}
这是我的第一个用户输入的样子：
user := models.User{Email: "admin@site.com.uy",Password: "password",CreatedAt: time.Now(),}
我从数据库中得到的错误如下：
"sql: Scan error on column index 8, name "password_changed_at": unsupported Scan, storing driver.Value type
into type *time.Time"
还有类似 "ProfileImage" 的错误。
这是我创建 Users 表的代码：
_, err = db.Exec(`CREATE TABLE IF NOT EXISTS users (id INT NOT NULL AUTO_INCREMENT,email VARCHAR(255) NOT NULL,password VARCHAR(255) NOT NULL,profile_image VARCHAR(255) DEFAULT '',password_changed_at TIMESTAMP DEFAULT NULL,created_at TIMESTAMP NOT NULL,PRIMARY KEY (id))`)
我尝试将 password_changed_at 改为 DEFAULT CURRENT_TIMESTAMP，但是又遇到了另一个错误：
Error 1292: Incorrect datetime value: '0000-00-00' for column 'password_changed_at' at row 1
编辑：
我通过在 password_changed_at 的 db 标签中添加 omitempty 来修复了该错误，如下所示：
PasswordChangedAt time.Time `db:"password_changed_at" json:"password_changed_at,omitempty"`
但是现在，每当我获取一个用户时，我会遇到以下错误：
{"error": "sql: Scan error on column index 8, name \"password_changed_at\": unsupported Scan, storing driver.Value type <nil> into type *time.Time"}
你能调整 SQL 代码以创建符合所需模型的 users 表吗？或者指出我看到的任何错误。
英文:
I have a Gin and upper/db MySQL ORM for the Rest API server. I have created a simple script to create the database and tables after "first deploy" or something like that. But I keep getting errors from SQL.
This is my User Model:
type User struct {ID int64 `db:"id,omitempty" json:"id,omitempty"`Email string `db:"email" json:"email"`Password string `db:"password" json:"password,omitempty"`ProfileImage string `db:"profile_image" json:"profile_image"`PasswordChangedAt time.Time `db:"password_changed_at" json:"password_changed_at,omitempty"`CreatedAt time.Time `db:"created_at" json:"created_at"`}
And this is what my first user input looks like:
user := models.User{Email: "admin@site.com.uy",Password: "password",CreatedAt: time.Now(),}
I am getting errors from the database like:
> "sql: Scan error on column index 8, name "password_changed_at": unsupported Scan, storing driver.Value type <nil> into type *time.Time"
Also got the error with "ProfileImage" etc.
This is my Users table creation:
_, err = db.Exec(`CREATE TABLE IF NOT EXISTS users (id INT NOT NULL AUTO_INCREMENT,email VARCHAR(255) NOT NULL,password VARCHAR(255) NOT NULL,profile_image VARCHAR(255) DEFAULT '',password_changed_at TIMESTAMP DEFAULT NULL,created_at TIMESTAMP NOT NULL,PRIMARY KEY (id))`)
I have tried changing the password_changed_at to DEFAULT CURRENT_TIMESTAMP but it gives me another error:
> Error 1292: Incorrect datetime value: '0000-00-00' for column 'password_changed_at' at row 1
Edit:
I fixed that error by adding omitempty to db tag for password_changed_at like:
PasswordChangedAt time.Time `db:"password_changed_at" json:"password_changed_at,omitempty"`.
But now, whenever I get a user I get the following error:
{"error": "sql: Scan error on column index 8, name \"password_changed_at\": unsupported Scan, storing driver.Value type <nil> into type *time.Time"}
Could you adjust the SQL code to create the users table for the desired model? Or indicate me any errors you see.
答案1
得分: 1
这个错误通常是由于尝试创建两个具有不同时间戳值的日期字段引起的。为了解决这个问题，我只需在受影响的表中添加以下查询。
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;SET sql_mode = '';SET GLOBAL sql_mode = '';
然后，您可以再次尝试编写您的CREATE表查询。
英文:
This error is normally caused by trying to create two date field with different timestamp values. To fix this I just add the following query in the affected table.
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;SET sql_mode = '';SET GLOBAL sql_mode = '';
Then you can try againg to write your CREATE table query
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论