英文:
golang calling mysql function
问题
我有一个现有的MySQL表，它使用MySQL的UUID_SHORT()函数生成唯一的ID。表的简化版本如下：
CREATE TABLE `users` (`user_uuid` bigint(20) unsigned NOT NULL,`user_name` varchar(64) NOT NULL);
要创建一个新用户，可以使用以下语句：
INSERT INTO users (user_uuid, user_name) VALUES (UUID_SHORT(), "new user name");
我开始使用gorm实现数据库模型，但不知道如何告诉gorm和database/sql在创建User的新实例时调用UUID_SHORT()。
在model/users.go文件中：
package modeltype User struct {UserUUID uint64 `gorm:"column:user_uuid;primary_key:yes";sql:"notnull;default:uuid_short"`UserName string `sql:"notnull"`}func (user User) TableName() string {return "users"}
在model/users_test.go文件中：
package model_testimport ("testing"".../model"".../model/testutil")func TestUserCreate(t *testing.T) {user := model.User{// UserUUID: **在这里如何调用UUID_SHORT()?**,UserName: "Go Test",}// testutil.DB是gorm.Open("mysql", ...)的成功结果testutil.DB.Create(&user)}
在保存实例时，如何调用UUID_SHORT()来为user_uuid列生成值？
英文:
I have an existing mysql table, which uses mysql's UUID_SHORT() function to generate unique IDs. A simplified version of the table:
CREATE TABLE `users` (`user_uuid` bigint(20) unsigned NOT NULL,`user_name` varchar(64) NOT NULL);
And a new user would be created via:
INSERT INTO users (user_uuid, user_name) values (UUID_SHORT(), "new user name");
I started to implement the DBs model using gorm and I'm drawing a blank on how to tell gorm and database/sql to call UUID_SHORT() when a new instance of User is created.
From model/users.go:
package modeltype User struct {UserUUID uint64 `gorm:"column:user_uuid;primary_key:yes";sql:"notnull;default:uuid_short"`UserName string `sql:"notnull"`}func (user User) TableName() string {return "users"}
From model/users_test.go:
package model_testimport ("testing"".../model"".../model/testutil")func TestUserCreate(t *testing.T) {user := model.User{// UserUUID: **HOW DO I CALL UUID_SHORT() HERE?**,UserName: "Go Test",}// testutil.DB is the successful result of gorm.Open("mysql", ...)testutil.DB.Create(&user)}
How can I call UUID_SHORT() for the user_uuid column when the instance is saved?
答案1
得分: 3
为了在model.User调用中使用MySQL的UUID_SHORT()函数，似乎需要在达到testutil.DB.Create(&user)这一行之前，对数据库进行额外的SQL调用。
使用gorm来实现MySQL的UUID_SHORT()调用可能是一个不错的选择，但这可能需要更多的工作，仅仅是为了映射一个单独的行值（在这种情况下是user_uuid），以便在model.User中使用（可能需要额外的struct来处理UUID_SHORT()）。
因此，使用一个更简单的方法可能会有所帮助。以下代码是一个基本的（尽管可能不太好的）示例，展示了如何使用sql包来获取UUID_SHORT()（在这种情况下使用了get_uuid_short()自定义函数）：
package model_testimport ("database/sql"_ "github.com/go-sql-driver/mysql""github.com/jinzhu/gorm""github.com/stretchr/testify/assert""log""testing")type User struct {UserUUID uint64 `gorm:"column:user_uuid;primary_key:yes";sql:"notnull;default:uuid_short"`UserName string `sql:"notnull"`}func (user User) TableName() string {return "users"}func get_uuid_short() uint64 {var uuid_short uint64/* 使用`sql`包连接到数据库 */db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/uuid_test")if err != nil {log.Fatal(err)}/* 查询UUID_SHORT() */rows, err := db.Query("select UUID_SHORT()")if err != nil {log.Fatal(err)}defer db.Close()/* 获取uuid_short的值 */for rows.Next() {err := rows.Scan(&uuid_short)if err != nil {log.Fatal(err)}}return uuid_short}func TestUserCreate(t *testing.T) {user := User{UserUUID: get_uuid_short(), /* 获取下一个UUID_SHORT()的值 */UserName: "Go Test",}db, err := gorm.Open("mysql", "username:password@/uuid_test?charset=utf8&parseTime=True&loc=Local")db.Create(&user)assert.Nil(t, err)}
在MySQL表中的结果如下：
mysql> select * from users;+-------------------+-----------+| user_uuid | user_name |+-------------------+-----------+| 24070794506141712 | Go Test || 24070794506141713 | Go Test || 24070794506141714 | Go Test |+-------------------+-----------+3 rows in set (0.00 sec)
注意：此代码可能无法处理多个用户同时调用get_uuid_short()的情况（尽管MySQL可能已经提供了处理这种情况的方法）。
英文:
In order to make a call to MySQL's UUID_SHORT() in your model.User call, it would seem that you'd need to make an additional SQL call to the database before reaching the testutil.DB.Create(&user) line at some point.
It might be nice to find a way to use gorm for the MySQL UUID_SHORT() call itself, but that might end up requiring more work just to map a single row value (user_uuid in this case) for use in your model.User (possibly requiring an additional struct just for UUID_SHORT()).
Therefore, using a simpler approach might help. The following code is a basic (though perhaps poor*) example of how UUID_SHORT() could be found using the sql package (specifically with the get_uuid_short() user-defined function, in this case):
package model_testimport ("database/sql"_ "github.com/go-sql-driver/mysql""github.com/jinzhu/gorm""github.com/stretchr/testify/assert""log""testing")type User struct {UserUUID uint64 `gorm:"column:user_uuid;primary_key:yes";sql:"notnull;default:uuid_short"`UserName string `sql:"notnull"`}func (user User) TableName() string {return "users"}func get_uuid_short() uint64 {var uuid_short uint64/* connect to db using `sql` package */db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/uuid_test")if err != nil {log.Fatal(err)}/* select UUID_SHORT() */rows, err := db.Query("select UUID_SHORT()")if err != nil {log.Fatal(err)}defer db.Close()/* get value of uuid_short */for rows.Next() {err := rows.Scan(&uuid_short)if err != nil {log.Fatal(err)}}return uuid_short}func TestUserCreate(t *testing.T) {user := User{UserUUID: get_uuid_short(), /* get next UUID_SHORT() value */UserName: "Go Test",}db, err := gorm.Open("mysql", "username:password@/uuid_test?charset=utf8&parseTime=True&loc=Local")db.Create(&user)assert.Nil(t, err)}
With results like this in the MySQL table:
mysql> select * from users;+-------------------+-----------+| user_uuid | user_name |+-------------------+-----------+| 24070794506141712 | Go Test || 24070794506141713 | Go Test || 24070794506141714 | Go Test |+-------------------+-----------+3 rows in set (0.00 sec)
*Note: This code might not address times where multiple users are concurrently calling get_uuid_short() (though MySQL may already provide a way to deal with this).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论