• InnoDB: unable to lock / ibdata1 error


    Error in MySQL startup under OS X environment: 016-03-03T00:02:30.483037Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 35 2016-03-03T00:02:30.483100Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. The terminal repeatedly prints the above error log. From the error log, it seems that […]

  • The influence of null on index in MySQL


    preface I have read many blogs and heard many people say, including our company’s DBA, that if a column in MySQL contains null, then the index containing the column is invalid. After reading the second edition of high performance mysql and the second edition of InnoDB storage engine, I didn’t see the introduction about this. […]

  • Simple understanding of MySQL dialect


    Apart from the database, what is the dialect in life? Dialect is the characteristic language of a certain place, which is different from other places. Only a small part of you can understand it. This place is another dialect. The same is true of database dialect. MySQL is a dialect, Oracle is a dialect, and […]

  • MySQL trigger concept, principle and usage


    This paper introduces the concept, principle and usage of MySQL trigger. The details are as follows: 1. The concept of trigger Trigger is a method provided by MySQL for programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by program or […]

  • The concept, principle and common usage of MySQL stored procedure


    This paper introduces the concept, principle and common usage of MySQL stored procedure. The details are as follows: 1. The concept of stored procedure In some languages, such as Pascal, there is a concept called “procedure” and “function”. In PHP, there is no procedure, only function. Procedure: encapsulates several statements, which are executed when calledFunction: […]

  • The concept and usage of MySQL cursor


    This paper introduces the concept and usage of MySQL cursor. The details are as follows: 1. The concept of cursors One SQL corresponds to n resources. The interface for fetching the resources is the cursor. Along the cursor, one line can be fetched at a time. If you have developed Android, you should know that […]

  • Detailed explanation of MySQL user and authority management


    This paper introduces the management of MySQL users and permissions. The details are as follows: When users connect to MySQL, they can do all kinds of queries, which are maintained by MySQL users and permissions. The interaction between users and database server is divided into two stages (1) Do you have the right to connect(2) […]

  • Two query methods when the field type of MySQL query is JSON


    The table structure is as follows: id varchar(32) info json Data: id = 1 Info = {“age”: “18”, “disname”: “Xiao Ming”} ——————————————– Now I need to get the value of disanme in info. The query methods are as follows: 1. select t.id,JSON_EXTRACT(t.info,’$.disname’) as disname from tableName t where 1=1 result: Id = 1, disname = […]

  • MySQL full text index to achieve a simple version of the search engine instance code


    preface Only InnoDB and MyISAM storage engines can use full-text indexing (InnoDB supports full-text indexing from mysql5.6) Char, varchar and text type fields can create full text index type Full text index based on keywords, how to distinguish different keywords, we need to use the word segmentation (stopword) English words are segmented with spaces and […]

  • MySQL solves the problem of database n + 1 query


    brief introduction In ORM framework, such as hibernate and mybatis, you can set association objects, such as user object association DeptIf you query n users, you need to query dept n times. The query user is a select and the query user is associatedIt’s n times, so it’s N + 1. In fact, it’s more […]

  • MySQL 8.0.16 winx64 and Linux’s method of modifying root user’s password


    Connect the database and other basic operations, please solve by yourself. This article focuses on how to change the password. 1、 Query user password: Command to query user password: select host, user, authentication_string from mysql.user ; Host: the IP ‘location’ where users are allowed to log in indicates that they can log in remotely; User: […]

  • MySQL version 8.0.16 installation prompt does not use “utf8b3” but “utf8b4”


    In MySQL 8.0.16, there may be some errors when installing, indicating that “utf8b3” has been used instead of “utf8b4” #/////////////////////The following is a summary my.ini Content in////////////////////// [mysqld] #Set port 3306 port=3306 #Set the installation directory of MySQL Basedir = # remember to fill in here #Set the data storage directory of MySQL database (this […]