MySQL中IF EXISTS ELSE语句语法错误排查求助

Let's break down the problems in your code and fix them step by step:

Key Issues Identified #

• Invalid INSERT Syntax: You can't use a WHERE clause with INSERT — that's only for filtering rows in SELECT/UPDATE/DELETE. Your goal is to insert a new row for the given email, so you need to include the Email column in your INSERT statement.
• Missing Procedure Parameter: Your stored procedure lastlogin() doesn't define the @mail parameter it uses. You need to declare an input parameter for the email value.
• Invalid DateTime Format: MySQL's DATETIME type expects values in YYYY-MM-DD or YYYY-MM-DD HH:MM:SS format. '00.00.0000' is not valid; use '0000-00-00' if you want a "no login" placeholder.
• Table Name Inconsistency: You reference both fenutzer and user in your procedure — pick one consistent table name (I'll assume user as your main table).
• Incomplete Logic: Your current code only inserts a row in the ELSE case but doesn't return the LastLogin value after insertion.

Corrected Stored Procedure #

DELIMITER //
CREATE PROCEDURE lastlogin(IN p_mail VARCHAR(255)) -- Declare input parameter for email
BEGIN
    -- Check if user with the given email exists
    IF EXISTS (SELECT 1 FROM `user` WHERE Email = p_mail) THEN
        -- Return existing LastLogin if user exists
        SELECT LastLogin FROM `user` WHERE Email = p_mail;
    ELSE
        -- Insert new user record with the email and default LastLogin
        INSERT INTO `user`(Email, LastLogin) VALUES (p_mail, '0000-00-00');
        -- Return the default LastLogin after insertion
        SELECT '0000-00-00' AS LastLogin;
    END IF;
END //
DELIMITER ;

How to Call the Procedure #

-- Replace 'user@example.com' with your actual email value
CALL lastlogin('user@example.com');

Additional Notes #

• If you want to use the current timestamp as the default LastLogin instead of '0000-00-00', replace the insert value with CURRENT_TIMESTAMP.
• Make sure the Email column in your user table is properly indexed (e.g., a unique index) to avoid duplicate insertions if the procedure is called concurrently for the same email.
• In your C# code, you can call this stored procedure using MySqlCommand with CommandType.StoredProcedure, and pass the email as a parameter (avoid concatenating values directly to prevent SQL injection).

内容的提问来源于stack exchange，提问作者catcat