h2 mysql sql兼容,h2在MySQL兼容模式下不强制执行NOT NULL

In MySQL compatibility mode, the following SQL succeeds and returns 0:

CREATE TABLE test2 (i INTEGER NOT NULL);

INSERT INTO test2 VALUES (NULL);

SELECT * FROM test2;

It fails as expected in the default mode. It also fails with MySQL 5.5 / InnoDB. Does "MySQL compatibility" actually mean "MyISAM compatibility"?

解决方案

This problem is due to both the way MySQL handles Null values and h2. Basically MySQL converts nulls received to empty string/0 where as in h2 there is clear distinction among all three types.

So, they(h2) have decided to convert the nulls to 0/empty string/current time stamp in MYSQL mode. But since the as per core h2 0/empty string are not nulls, so it inserts the data.

Use org.h2.engine.Mode and modify the variable related to this:

Mode mode = Mode.getInstance("MYSQL");

mode.convertInsertNullToZero = false;

This has worked for me. Thanks to this link.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
好的,这里给您提供一个简单的示例代码,帮助您实现基本的注册和登录功能,使用 Python Flask 框架和 MySQL 数据库: 1. 安装所需的 Python 库 ``` pip install Flask flask-mysqldb ``` 2. 创建一个名为 `app.py` 的文件,并在其中添加以下代码: ```python from flask import Flask, render_template, request, redirect, session from flask_mysqldb import MySQL import bcrypt app = Flask(__name__) # 设置 session 密钥 app.secret_key = 'secret_key' # MySQL 配置 app.config['MYSQL_HOST'] = 'localhost' app.config['MYSQL_USER'] = 'root' app.config['MYSQL_PASSWORD'] = 'password' app.config['MYSQL_DB'] = 'users' mysql = MySQL(app) # 用户注册 @app.route('/register', methods=['GET', 'POST']) def register(): if request.method == 'POST': name = request.form['name'] email = request.form['email'] password = request.form['password'].encode('utf-8') hashed_password = bcrypt.hashpw(password, bcrypt.gensalt()) # 插入用户信息到数据库中 cur = mysql.connection.cursor() cur.execute("INSERT INTO users(name, email, password) VALUES (%s, %s, %s)", (name, email, hashed_password)) mysql.connection.commit() cur.close() session['name'] = name session['email'] = email return redirect('/') return render_template('register.html') # 用户登录 @app.route('/login', methods=['GET', 'POST']) def login(): if request.method == 'POST': email = request.form['email'] password = request.form['password'].encode('utf-8') # 从数据库中获取用户信息 cur = mysql.connection.cursor() cur.execute("SELECT * FROM users WHERE email = %s", [email]) user = cur.fetchone() cur.close() # 验证密码是否正确 if user and bcrypt.checkpw(password, user[3].encode('utf-8')): session['name'] = user[1] session['email'] = user[2] return redirect('/') else: return render_template('login.html', error='Invalid email or password') return render_template('login.html') # 用户注销 @app.route('/logout') def logout(): session.clear() return redirect('/') # 首页 @app.route('/') def index(): if 'name' in session: return render_template('index.html', name=session['name'], email=session['email']) else: return redirect('/login') if __name__ == '__main__': app.run(debug=True) ``` 3. 在项目根目录下创建一个名为 `templates` 的文件夹,并在其中创建以下三个 HTML 模板文件: - `register.html`,用于用户注册页面 ```html <!DOCTYPE html> <html> <head> <title>Register</title> </head> <body> <h2>Register</h2> <form method="POST" action="/register"> <input type="text" name="name" placeholder="Name"><br><br> <input type="email" name="email" placeholder="Email"><br><br> <input type="password" name="password" placeholder="Password"><br><br> <input type="submit" value="Register"> </form> </body> </html> ``` - `login.html`,用于用户登录页面 ```html <!DOCTYPE html> <html> <head> <title>Login</title> </head> <body> <h2>Login</h2> {% if error %} <p style="color: red;">{{ error }}</p> {% endif %} <form method="POST" action="/login"> <input type="email" name="email" placeholder="Email"><br><br> <input type="password" name="password" placeholder="Password"><br><br> <input type="submit" value="Login"> </form> </body> </html> ``` - `index.html`,用于用户登录后的首页 ```html <!DOCTYPE html> <html> <head> <title>Homepage</title> </head> <body> <h2>Welcome, {{ name }}!</h2> <p>Your email is: {{ email }}</p> <a href="/logout">Logout</a> </body> </html> ``` 4. 在 MySQL 数据库中创建一个名为 `users` 的表,用于存储用户信息: ```sql CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `email` varchar(100) NOT NULL, `password` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` 现在您可以运行 `app.py`,在浏览器中访问 `http://localhost:5000/register` 进行用户注册,访问 `http://localhost:5000/login` 进行用户登录,访问 `http://localhost:5000/logout` 进行用户注销。登录后访问 `http://localhost:5000/` 可以看到用户的个人主页。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值