La definición de "modos" está al principio del capitulo dedicado al tema en el manual:
Cita: 6.1.7 Server SQL Modes
The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.
Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.
Cita: El MySQL Server puede operar en diferentes modos de SQL, y estos modos pueden aplicarse en forma diferente para diferentes clientes, dependiendo del valor de la variable SQL_MODE. Los DBA pueden ajustar el modo global de SQL para a los requerimientos del servidor operativo en el server del sitio, y cada aplicación puede ajustar su modo SQL de sesión para sus propios requerimientos.
Los modos SQL afectan la sintaxis e SQL soportado por MySQL y las validaciones de datos que realiza. Esto hace a MySQL facil de usar en diferentes ambientes y utilziar MySQL junto a otros servidores de BBDD..
Para hacértelo mas o menos simple: Si en algún caso tienes dos DBMS ,que trabajan en forma concurrente, y el segundo tiene reglas específicas de sintaxis, puedes sin mayores inconvenientes ajustar el comportamiento de MySQL a las reglas del otro servidor, de modo que haya consistencia en sus comportamientos, y no tengas que apelar a reglas de programación específica para trabajar con ambos servidores a la vez.
¿Se va entendiendo?
El modo sobre el que preguntas apunta a hacer que MySQL se comporte de un modo determinado ante valores que no pasan las restricciones de tipos de datos.
¿Cuando lo usas?
Dependerá de cuando lo
necesites usar...
En el diseño y administración de BBDD, no hay reglas fijas. Depende de qué es lo que se necesite hacer, en qué momento, para qué requerimientos y sobre qué base de datos.